Forum Discussion
Multi-level constraints
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.
- MCarr10Mar 27, 2022Copper ContributorI 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- eli_kentApr 01, 2022Copper Contributor
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.
- eli_kentMar 27, 2022Copper Contributor
Hi, and thanks for taking this on!
Firstly, can you kindly paste the diagram into the body of the message? My employer has a strict policy about opening files.
Secondly, to clarify, workers have one or more trades, and trades can have zero specific skills/specifications or can have one or more.
So Mike (worker) could be a carpenter (trade) with no special skills/specifications.
Bob (worker) could be a laborer (trade) who knows how to use a pile driver (skill) and an operator (trade) who knows how to use a digger (skill) and a crane (skill)
Barbara (worker) could be an electrician (trade) with a master certificate (skill)
I hope that makes it more clear.