sql server 2005 - Add a SQL XOR Constraint between two nullable FK's

Question

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?

Answer 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.