How to use constraints to force two child items be from the same parent?-CodePudding

I have a Jobs table that holds jobs.
I have a Tasks table that holds tasks that belong to a job (1:many).
I have a Task_Relationships table that holds the data about which tasks depend on other tasks within a job.

I have 2 jobs, each job has 3 tasks and within the jobs the tasks are related as in the diagram. The Task_Relationships table is to represent that tasks within a job have dependencies between them.

How to ensure that when I add an entry to the Task_Relationships table say (1,2) representing the fact that task 1 is related to task 2, that tasks 1 and 2 are in the same job? I'm trying to enforce this through keys and not through code.

drop table if exists dbo.test_jobs
create table dbo.test_jobs (
    [Id] int identity(1,1) primary key not null,
    [Name] varchar(128) not null
)

drop table if exists dbo.test_tasks  
create table dbo.test_tasks (
    [Id] int identity(1,1) primary key not null,
    [Job_Id] int not null,
    [Name] varchar(128) not null
    constraint fk_jobs foreign key ([Id]) references dbo.test_jobs(Id)
)

drop table if exists dbo.test_task_relationships 
create table dbo.test_task_relationships (
    [Id] int identity(1,1) not null,
    [From_Task] int not null,
    [To_Task] int not null
    constraint fk_tasks_from foreign key ([From_Task]) references dbo.test_tasks(Id),
    constraint fk_tasks_to foreign key ([To_Task]) references dbo.test_tasks(Id)
)

CodePudding user response：

A reliance on identity columns as primary keys is not helping you here. And it is a logic fault to use an identity column in the relationship table IMO. Surely you do not intend to allow multiple rows to exist in that table with the same values for <from_task, to_task>.

Imagine the child table defined as:

create table dbo.test_tasks (
    Job_Id int not null,
    Task_Id tinyint not null,
    Name varchar(128) not null,
    constraint pk_tasks primary key clustered (Job_Id, Task_Id),
    constraint fk_jobs foreign key ([Job_Id]) references dbo.test_jobs(Id)
);

Now your relationship table can be transformed into:

create table dbo.test_task_relationships (
    From_Job int not null,
    From_Task tinyint not null,
    To_Job int not null,
    To_Task tinyint not null
);

I'll leave it to you to complete the DDL but that should make your goal trivial.

CodePudding user response：

You can declare a superkey in the Task table that includes the Job_Id column as well as columns from an existing key.

create table dbo.test_tasks (
    [Id] int identity(1,1) primary key not null,
    [Job_Id] int not null,
    [Name] varchar(128) not null
    constraint fk_jobs foreign key ([Id]) references dbo.test_jobs(Id),
    constraint UQ_Tasks_WithJob UNIQUE (Id, Job_Id)
)

You can then add the Job_Id column to the relationships table and include it in both foreign key constraints:

create table dbo.test_task_relationships (
    [Id] int identity(1,1) not null,
    [From_Task] int not null,
    Job_Id int not null,
    [To_Task] int not null
    constraint fk_tasks_from foreign key ([From_Task], Job_Id) references dbo.test_tasks(Id, Job_Id),
    constraint fk_tasks_to foreign key ([To_Task], Job_Id) references dbo.test_tasks(Id, Job_Id)
)

There is now no way for the table to contain mismatched tasks. If necessary, wrap this table in a view/trigger if you don't want to expose the presence of the job_id column to applications and to automatically populate it during insert.