13

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?

proggrock
  • 3,239
  • 6
  • 36
  • 51

3 Answers3

21

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.

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • 4
    simpler: `CHECK(fk1 IS NULL != fk2 IS NULL)` – Stephen Fuhry Jan 10 '15 at 21:57
  • 1
    @StephenJ.Fuhry Sadly, MS SQL Server doesn't treat Boolean type as a first class citizen, so that syntax won't be accepted. – Branko Dimitrijevic Jan 18 '15 at 04:05
  • 1
    but it does work in PostgreSQL, which is what i need it for. thanks for the short expression, @StephenJ.Fuhry. of course, it would be better to have XOR. given how much other stuff has been piled into the system that seems an odd omission. – mARK Oct 23 '15 at 23:30
1

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".
dani herrera
  • 48,760
  • 8
  • 117
  • 177
  • 1
    The `coalesce(a, b)` will return non-NULL when **both** `a` and `b` are non-NULL and the CHECK will pass, which it shouldn't (OP was explicit that both can't have values). In other words, the `insert into #t values (1, 1)` should fail. – Branko Dimitrijevic Jun 20 '12 at 20:37
  • the only limitation is the arithmetic overflow (from `a*b`), which can be removed if `a|b` is used instead – PLopes Jun 29 '20 at 20:57
-1

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.

user98534
  • 195
  • 3
  • 4
  • 9