Add a SQL XOR Constraint between two nullable FK&#

问题:

I'd like to define a constraint between two nullable FK's in a table where if one is null the other needs a value, but both can't be null and both can't have values. Logic is the derived table inherits data from the either of the FK tables to determine its type. Also, for fun bonus points, is this a bad idea?

回答1:

One way to achieve it is to simply write down what "exclusive OR" actually means:

CHECK (
    (FK1 IS NOT NULL AND FK2 IS NULL)
    OR (FK1 IS NULL AND FK2 IS NOT NULL)
)

However, if you have many FKs, the above method can quickly become unwieldy, in which case you can do something like this:

CHECK (
    1 = (
        (CASE WHEN FK1 IS NULL THEN 0 ELSE 1 END)
        + (CASE WHEN FK2 IS NULL THEN 0 ELSE 1 END)
        + (CASE WHEN FK3 IS NULL THEN 0 ELSE 1 END)
        + (CASE WHEN FK4 IS NULL THEN 0 ELSE 1 END)
        ...
    )
)

BTW, there are legitimate uses for that pattern, for example this one (albeit not applicable to MS SQL Server due to the lack of deferred constraints). Whether it is legitimate in your particular case, I can't judge based on the information you provided so far.

回答2:

You can use check constraint:

create table #t (
   a int,
   b int);

alter table #t add constraint c1 
check ( coalesce(a, b) is not null and a*b is null );

insert into #t values ( 1,null);

insert into #t values ( null ,null);

Running:

The INSERT statement conflicted with the CHECK constraint "c1". 

回答3:

Alternate way is to define this check constraint in a procedure. Before you insert a record in the derived table, the constraint should be satisfied. Else insert fails or returns an error.