Есть Города, Типы Работ и Задачи. В городах может быть несколько типов работы. Задачи создаются для города, в котором может быть несколько типов должностей. У городов может быть много задач. Но при добавлении типов работы для задач, назначенных городу, необходимо убедиться, что город имеет этот тип работы.
Как создать связь / ограничение при добавлении / обновлении Task_JobTypes, чтобы убедиться, что город, связанный с задачей, имеет этот тип работы, разрешенный в City_JobTypes? Ограничение в Task_JobTypes «FK_Task_JobTypes_JobTypes» должно ссылаться на это, а не только на JobTypes.
Города - идентификатор, имя
JobTypes - идентификатор, имя
CityJobTypes - CityId, JobTypeId (разрешенные типы вакансий для каждого города)
Задачи - Id, CityId, Name (Задача для города)
TaskJobTypes - TaskId, JobTypeId (Типы заданий для каждой задачи)
Таблицы -
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
Просто добавление еще одного столбца (CityId) в TaskJobTypes, который ссылается на город, на который задача уже ссылается, кажется излишним. У всего остального уже есть отношения, это единственный способ? Это всего лишь один пример из многих таблиц в базе данных, которые имеют подобные настройки, и добавление дополнительного столбца кажется излишним, но если это единственный вариант (или самый простой / самый чистый), это то, что я пытался увидеть.
Иногда, если я добавляю еще один столбец в таблицу для обеспечения такого ограничения (например, здесь CityId
), я назову таблицу _TaskJobTypes
вместо этого, столбец _CityId
, а затем создаю представление с именем TaskJobTypes
, которое скрывает столбец (плюс триггеры). для заполнения этого столбца во время INSERT
/ UPDATE
). Ты знает, что ваш db имеет дополнительный столбец, пользователи вашей базы данных не требуется (поскольку они по-прежнему просто INSERT
/ UPDATE
/ DELETE
против этого представления вместо таблицы)
How to create Relationship/Constraint when Adding/Updating Task_JobTypes to make sure the City associated with the Task has that Job Type allowed in City_JobTypes?
Вы можете сделать это с помощью ПРОВЕРИТЬ ОГРАНИЧЕНИЕ, который вызывает функцию. Функция будет принимать TaskID
и JobTypeID
в качестве параметров и запрашивать таблицу Tasks
, присоединенную к таблице City_JobTypes
, чтобы узнать, есть ли у этого City
этот JobType
. Тогда функция вернет True (1)
, если в городе есть тип задания, или false (0)
, если нет.
В качестве альтернативы вы также можете сделать это с помощью TRIGGER, но я предпочитаю ПРОВЕРИТЬ ОГРАНИЧЕНИЯ лично.
Проблема с выполнением этого с помощью такого ограничения проверки заключается в том, что это не мешает кому-либо удаление строку из City_JobTypes
, на которую "ссылается" строка в Task_JobTypes
.
@Damien_The_Unbeliever Истинно, если строки могут быть удалены из этой таблицы, то ссылочная целостность должна обрабатываться триггером.
Мне кажется, что вы философски возражаете против composite keys
. Таблица ссылок City_JobTypes
имеет составной первичный ключ CityId, JobTypeId
. Любая другая таблица, которая ограничивает City_JobTypes
, должна ограничиваться своим первичным ключом. Это два столбца, но это все равно один ключ. Я не вижу в этом проблемы.
Я вижу вашу структуру ...
Task
принадлежит одному City
и имеет один JobType
.
Поскольку задание должно иметь одинCity
и одинJobType
, сделать эти атрибуты Task
?
City
↑
Task → JobType
City
также имеет 0 ... много JobTypes
, которые "разрешены".
City ← City_JobTypes
↑ ↓
Task → JobType
На данный момент ваш стол Task
уже имеет CityID
и JobTypeID
.
Почему бы не ограничить этот составной ключ таблицей City_JobTypes
?
City ← City_JobTypes
↑ ↗ ↓
Task → JobType
Если на одном Task
может быть 0 ... много JobTypes
...
Я бы начал здесь, я не вижу никакого способа ограничить Tasks
на основе ссылок City_JobType
...
City ← City_JobTypes↘
↑ JobType
Task ← Task_JobTypes↗
Тогда я решил, что Task
можно разумно идентифицировать как CityTask
с составным первичным ключом. Это позволило бы следующее.
City ← City_JobTypes ↘
↑ ↑ ↑ JobType
CityTask ← CityTask_JobTypes↗
Почему «без добавления повторяющегося столбца»? Без этой информации это невозможно. Вам следует добавить
JobType
в список задач, связанных с городом. Затем вы можете ограничить это для всех таблиц, которые вам нужны, используя составные ключи.