SQL: Allow NULL on two columns but not both

Question

I have a table that is simply a lookup with two columns.

spiceId - INT
spiceDes - VARCHAR(100)

Now, I have the columns set as allow NULL for both columns, but I would like to add a constraint where it should be that only one column could be NULL for any record. That is spiceID and spiceDes could not, both be NULL.

How can I add this constraint?

Answer 1

Use Alter table to add a check constraint on your table:

ALTER TABLE tableName
ADD  CONSTRAINT CK_nulltest
CHECK (spiceId IS NOT NULL OR spiceDes IS NOT NULL);

Answer 2

What about CHECK Constraints?

ADD CONSTRAINT chkIsNotNull CHECK (spiceId is not null or spiceDes is not null);