Forum Discussion
Multi-level constraints
I'm tracking workers in the construction industry. The industry is classified into trades - Laborer, Operating Engineer, Carpenter, etc. Each worker can have one or more trades. Within each trade ...
eli_kent If I understand the question correctly, the attached ER diagram should meet the requirements. Workers have one trade and a trade is associated with a skill.
I am not able to paste the image. What I can do is share the text of the tables with foreign keys.
CREATE TABLE [dbo].[SkillName](
[SkillName] [varchar](50) NOT NULL,
CONSTRAINT [PK_SkillName] PRIMARY KEY CLUSTERED
(
[SkillName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Trade](
[TradeName] [varchar](50) NOT NULL,
[SkillName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Trade] PRIMARY KEY CLUSTERED
(
[TradeName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Worker](
[WorkerName] [varchar](50) NOT NULL,
[TradeName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Worker] PRIMARY KEY CLUSTERED
(
[WorkerName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Trade] WITH CHECK ADD CONSTRAINT [FK_Trade_SkillName] FOREIGN KEY([SkillName])
REFERENCES [dbo].[SkillName] ([SkillName])
GO
ALTER TABLE [dbo].[Trade] CHECK CONSTRAINT [FK_Trade_SkillName]
GO
ALTER TABLE [dbo].[Worker] WITH CHECK ADD CONSTRAINT [FK_Worker_Trade] FOREIGN KEY([TradeName])
REFERENCES [dbo].[Trade] ([TradeName])
GO
ALTER TABLE [dbo].[Worker] CHECK CONSTRAINT [FK_Worker_Trade]
GO
CREATE TABLE [dbo].[SkillName](
[SkillName] [varchar](50) NOT NULL,
CONSTRAINT [PK_SkillName] PRIMARY KEY CLUSTERED
(
[SkillName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Trade](
[TradeName] [varchar](50) NOT NULL,
[SkillName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Trade] PRIMARY KEY CLUSTERED
(
[TradeName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Worker](
[WorkerName] [varchar](50) NOT NULL,
[TradeName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Worker] PRIMARY KEY CLUSTERED
(
[WorkerName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Trade] WITH CHECK ADD CONSTRAINT [FK_Trade_SkillName] FOREIGN KEY([SkillName])
REFERENCES [dbo].[SkillName] ([SkillName])
GO
ALTER TABLE [dbo].[Trade] CHECK CONSTRAINT [FK_Trade_SkillName]
GO
ALTER TABLE [dbo].[Worker] WITH CHECK ADD CONSTRAINT [FK_Worker_Trade] FOREIGN KEY([TradeName])
REFERENCES [dbo].[Trade] ([TradeName])
GO
ALTER TABLE [dbo].[Worker] CHECK CONSTRAINT [FK_Worker_Trade]
GO
MCarr10, that doesn't solve my issue.
I've got three many-to-many relationships, which are Worker-Trade, Trade-Skill, and Worker-Skill.
The rules are the following.
A worker cannot have one or more trades.
A worker zero or more skills for each trade.
A worker can only have a skill if he has the trade to which that skill belongs.
The way I understood it, the scheme you provided would work if workers could only have one trade and if each trade only could have one skill.
