Can a Check constraint relate to another table?

Question

Let´s say I have one table called ProjectTimeSpan (which I haven´t, just as example!) containing the columns StartDate and EndDate.

And that I have another table called SubProjectTimeSpan, also containing columns called StartDate and EndDate, where I would like to set a Check constraint that makes it impossible to set StartDate and EndDate to values "outside" the ProjectTimeSpan.StartDate to ProjectTimeSpan.EndDate

Kind of a Check constraint that knows about another tables values...

Is this possible?

Answer 1

You can create a user-defined function that does the check and returns 1 or 0, then create a check constraint on it, providing project id and the dates as the parameters.

Answer 2

In response to your comment on GSerg's answer, here's an example check constraint using a function:

alter table YourTable
add constraint chk_CheckFunction
check (dbo.CheckFunction() = 1)

Where you can define the function like:

create function dbo.CheckFunction()
returns int
as begin
    return (select 1)
end

The function is allowed to reference other tables.

Answer 3

Make a compound key of the ProjectTimeSpan table's key combined with the StartDate and EndDate columns, then use this compound key for your foreign key reference in your SubProjectTimeSpan table. This will give you the ability to write the necessary row-level CHECK constraints in the SubProjectTimeSpan table e.g.

CREATE TABLE ProjectTimeSpan 
(
 project_ID INTEGER NOT NULL UNIQUE, -- key
 StartDate DATE NOT NULL, 
 EndDate DATE NOT NULL, 
 CHECK (StartDate < EndDate), 
 UNIQUE (project_ID, StartDate, EndDate) -- compound key
 -- other project columns here...
);

CREATE TABLE SubProjectTimeSpan 
(
 project_ID INTEGER NOT NULL, 
 StartDate DATE NOT NULL, 
 EndDate DATE NOT NULL, 
 FOREIGN KEY (project_ID, StartDate, EndDate)
    REFERENCES ProjectTimeSpan (project_ID, StartDate, EndDate)
    ON DELETE CASCADE
    ON UPDATE CASCADE, 
 sub_StartDate DATE NOT NULL, 
 sub_EndDate DATE NOT NULL, 
 CHECK (sub_StartDate < sub_EndDate),
 CHECK (StartDate <= sub_StartDate), -- sub project can't start before main project
 CHECK (sub_EndDate <= EndDate)      -- sub project can't end after main project
 -- other sub project columns here...
);