时间戳

首页 > 解决方案 > 不添加重复列与辅助表的 SQL 关系

sql - 不添加重复列与辅助表的 SQL 关系

问题描述

有城市、工作类型和任务。城市可以有多种工作类型。为可以具有多种工作类型的城市创建任务。城市可以有许多任务。但是在为分配给城市的任务添加工作类型时，必须确保该城市具有该工作类型。

添加/更新 Task_JobTypes 时如何创建关系/约束以确保与任务关联的城市在 City_JobTypes 中允许该职位类型？Task_JobTypes "FK_Task_JobTypes_JobTypes" 中的约束需要引用它而不仅仅是 JobTypes。

城市 - ID、名称

JobTypes - ID、名称

CityJobTypes - CityId,JobTypeId（每个城市允许的工作类型）

任务 - Id,CityId,Name（城市任务）

TaskJobTypes - TaskId,JobTypeId（每个任务的 JobTypes）

表 - 

CREATE TABLE [dbo].[Cities](
    [Id] [int] IDENTITY(1,1) NOT NULL,  
    [Name] [varchar](500) NOT NULL, 
CONSTRAINT [PK_Cities] PRIMARY KEY CLUSTERED ([Id] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO    

CREATE TABLE [dbo].[JobTypes](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL
 CONSTRAINT [PK_JobTypes] PRIMARY KEY CLUSTERED ([Id] ASC)
 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO    

CREATE TABLE [dbo].[City_JobTypes](
    [JobTypeId] [int] NOT NULL,
    [CityId] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[City_JobTypes]  WITH CHECK ADD  CONSTRAINT [FK_City_JobTypes_Cities] FOREIGN KEY([CityId])
REFERENCES [dbo].[Cities] ([Id])
GO    

ALTER TABLE [dbo].[City_JobTypes]  WITH CHECK ADD  CONSTRAINT [FK_City_JobTypes_JobTypes] FOREIGN KEY([JobTypeId])
REFERENCES [dbo].[JobTypes] ([Id])
GO 

CREATE TABLE [dbo].[Tasks](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CityId] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Tasks] PRIMARY KEY CLUSTERED ([Id] ASC)
 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Tasks]  WITH CHECK ADD  CONSTRAINT [FK_Tasks_Cities] FOREIGN KEY([CityId])
REFERENCES [dbo].[Cities] ([Id])
GO

CREATE TABLE [dbo].[Task_JobTypes](
    [TaskId] [int] NOT NULL,
    [JobTypeId] [int] NOT NULL,
 CONSTRAINT [IX_Task_JobTypes-TaskId,JobTypeId] UNIQUE NONCLUSTERED 
(
    [TaskId] ASC,
    [JobTypeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Task_JobTypes]  WITH CHECK ADD  CONSTRAINT [FK_Task_JobTypes_JobTypes] FOREIGN KEY([JobTypeId])
REFERENCES [dbo].[JobTypes] ([Id])
GO

ALTER TABLE [dbo].[Task_JobTypes]  WITH CHECK ADD  CONSTRAINT [FK_Task_JobTypes_Tasks] FOREIGN KEY([TaskId])
REFERENCES [dbo].[Tasks] ([Id])
GO

标签： sqlsql-serverforeign-keysconstraintsrelationship

解决方案

在我看来，您在哲学上反对composite keys. 链接表City_JobTypes有一个复合主键CityId, JobTypeId。任何其他约束的表都City_JobTypes需要约束其主键。那恰好是两列，但它仍然是一个键。我没有看到那里的问题。


我看到你的结构的方式是......

ATask属于一个单一的City并且有一个单一的JobType。

作为一个Job必须有一个 City和一个 JobType，做出那些属性的Task？

City
 ↑
Task → JobType


ACity也有 0..manyJobTypes它是“允许的”。

City ← City_JobTypes
 ↑          ↓
Task → JobType


此时你的Task表已经有了 aCityID和 a JobTypeID。

为什么不将复合键限制在City_JobTypes表上呢？

City ← City_JobTypes
 ↑   ↗      ↓
Task → JobType


如果一个单Task人实际上可以有 0..many JobTypes...

我将从这里开始，我看不到任何方式来限制Tasks基于City_JobType链接的...

City ← City_JobTypes↘
 ↑                   JobType
Task ← Task_JobTypes↗

然后我决定Task可以合理地将其标识为CityTask具有复合主键的 a 。这将允许以下内容。

   City  ←    City_JobTypes ↘
   ↑          ↑     ↑        JobType
CityTask ← CityTask_JobTypes↗

推荐阅读