Search code examples
sqldatabasereferential-integrity

Factoring out nulls in bill-of-materials style relations

Given the schema

PERSON { name, spouse }

where PERSON.spouse is a foreign key to PERSON.name, NULLs will be necessary when a person is unmarried or we don't have any info.

Going with the argument against nulls, how do you avoid them in this case?

I have an alternate schema

PERSON { name }
SPOUSE { name1, name2 }

where SPOUSE.name* are FKs to PERSON. The problem I see here is that there is no way to ensure someone has only one spouse (even with all possible UNIQUE constraints, it would be possible to have two spouses).

What's the best way to factor out nulls in bill-of-materials style relations?

Solution

  • All right, use Auto-IDs and then use a Check Constraint. The "Name1" column (which would only be an int ID) will be force to only have ODD numbered IDs and Name2 will only have EVEN.

    Then create a Unique Constraint for Column1 and Column2.