Can a Check constraint relate to another table?

Question

Let's say I have one table called ProjectTimeSpan (which I haven't, just as an 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?

Immigrant

Beware solutions that use UDFs: dba.stackexchange.com/questions/12779/… – Messner

@Messner That question refers to a bug in SQL Server 2008 specifically. We are now 7 versions (8 if you count Azure) beyond that; that bug is long fixed. – Epicenter

Answer 1

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.

Ergosterol

and if I in the function wants to relate to an existing table like "ProjectTimeSpan.StartDate" I would put it like...? – Immigrant

@Jack Johnstone: Just change the select 1 part? You can pass parameters to the function – Ergosterol

This solution risks that rows modified in the source table could break referential integrity. You can avoid this risk by adding a trigger on the source table that checks the check constraint on the other table, like so. CREATE TRIGGER MyTrigger ON dbo.SourceTable AFTER UPDATE AS BEGIN ALTER TABLE dbo.ReferencingTable WITH CHECK CHECK CONSTRAINT TheConstraint END – Inlet

Answer 2

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.

Begorra

And how would that check constraint look like? – Immigrant

@Jack check (dbo.IsValidRange(project_id, start_date, end_date) = 1) – Begorra

GSerg: Presumably you would have to have a CHECK constraint using this UDF on both tables...? Please post some example code. – Endoplasm

@Endoplasm Yes, provided you want to change the parent project dates later. Which I could never imagine could be the case. See my comment to your answer. – Begorra

@GSerg: "change the parent project dates... I could never imagine could be the case" -- As a project manager myself I can assure you that the EndDate will change ;) – Endoplasm

@Marek-A- Do not waste everyone's time by quoting Oracle documentation in an SQL Server question. SQL Server supports user-defined functions in check constraints. – Begorra

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...
);

Endoplasm

This only makes sense if you want to allow changing the parent project dates... which does not make sense. Consider: A main project, taking 1 month to complete. Because of the financial crysis (sic) you had to delay it (both startdate and enddate must get +1 month). But you can't do it, because then the check on a child project will fail (child dates now outside parent dates). And you can't change the child project dates before changing its parents dates. Deadlock. – Begorra

Answer 4

You certainly can do this as many answers have shown. However, you should be aware that SQL Server seems to have trouble with CHECK CONSTRAINTs that use UDFs:

https://dba.stackexchange.com/questions/12779/how-are-my-sql-server-constraints-being-bypassed

Messner

Answer 5

You need to add constraint on the parent and the children table because the subproject can't be out of the project range but the project range can't move out of all the subproject too.

In these kind of situations, you should defer the check of the constraint on an upper level (webservice, application) with a transaction to ensure your data are in a valid state after multiple query on both table !

Taeniacide

Answer 6

It is absolutely possible, and actually quite simple. As per your example:

create or alter function dbo.Check_SubProjectTimeSpan_ProjectTimeSpan_Dates(
    @ProjectTimeSpanId int
  , @StartDate         date
  , @EndDate           date
)
returns bit
as
begin

if exists (select *
             from dbo.ProjectTimeSpan as pts
            where pts.Id        =  @ProjectTimeSpanId
              and pts.StartDate >= @StartDate
              and pts.EndDate   <= @EndDate)
begin
    return 1
end

return 0

end
go

alter table dbo.SubProjectTimeSpan add constraint
CK_SubProjectTimeSpan_ProjectTimeSpan_ProjectTimeSpanId_StartDate_EndDate
check (
    dbo.Check_SubProjectTimeSpan_ProjectTimeSpan_Dates(
        ProjectTimeSpanId, StartDate, EndDate) = 1
)

Please be aware however, that this will not be particularly performant.

Epicenter