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

• 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)
)

Answer

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.