Two foreign keys, only one can be not null at a time

I have a database table with two foreign keys pointing to two different tables. The business logic requires an “either or” relationship; only one foreign key can be NOT NULL at any given time.

Here are the four possible states the foreign keys can hold based on the business logic:

• NULL, NULL – okay
• number, NULL – okay
• NULL, number – okay
• number, number – invalid business logic

I’m using Django, and I know I can write some onSave() checks that will handle this, but that feels hackish.

Is there a better method to deal with this business logic?

Answer

Use a check constraint:

alter table t add constraint chk_table_fk1_fk2 on table
    check (fk1 is null or fk2 is null);