Связь SQL с дополнительной таблицей без добавления повторяющегося столбца

Есть Города, Типы Работ и Задачи. В городах может быть несколько типов работы. Задачи создаются для города, в котором может быть несколько типов должностей. У городов может быть много задач. Но при добавлении типов работы для задач, назначенных городу, необходимо убедиться, что город имеет этот тип работы.

Как создать связь / ограничение при добавлении / обновлении 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

Почему «без добавления повторяющегося столбца»? Без этой информации это невозможно. Вам следует добавить JobType в список задач, связанных с городом. Затем вы можете ограничить это для всех таблиц, которые вам нужны, используя составные ключи.

MatBailie 06.09.2018 15:49

Просто добавление еще одного столбца (CityId) в TaskJobTypes, который ссылается на город, на который задача уже ссылается, кажется излишним. У всего остального уже есть отношения, это единственный способ? Это всего лишь один пример из многих таблиц в базе данных, которые имеют подобные настройки, и добавление дополнительного столбца кажется излишним, но если это единственный вариант (или самый простой / самый чистый), это то, что я пытался увидеть.

user2828970 06.09.2018 16:06

Иногда, если я добавляю еще один столбец в таблицу для обеспечения такого ограничения (например, здесь CityId), я назову таблицу _TaskJobTypes вместо этого, столбец _CityId, а затем создаю представление с именем TaskJobTypes, которое скрывает столбец (плюс триггеры). для заполнения этого столбца во время INSERT / UPDATE). Ты знает, что ваш db имеет дополнительный столбец, пользователи вашей базы данных не требуется (поскольку они по-прежнему просто INSERT / UPDATE / DELETE против этого представления вместо таблицы)

Damien_The_Unbeliever 06.09.2018 16:30
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
3
469
2

Ответы 2

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 06.09.2018 16:33

@Damien_The_Unbeliever Истинно, если строки могут быть удалены из этой таблицы, то ссылочная целостность должна обрабатываться триггером.

Tab Alleman 06.09.2018 16:34

Мне кажется, что вы философски возражаете против 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↗

Другие вопросы по теме