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?

Are both columns allowed to be non-NULL? I.e. is this "at least one column must not be NULL" or "exactly one column must not be NULL"? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Aug 17, 2015 at 14:44
Neither. They both can be NOT NULL, but they both cannot be NULL. The only combinations allowed are NULL, NOT NULL or NOT NULL, NULL or NOT NULL, NOT NULL. Never it can be NULL, NULL. — PaulFrancis
– PaulFrancis, Commented Aug 18, 2015 at 7:53
How is that neither? It sounds exactly like "at least one column must not be NULL". — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Aug 18, 2015 at 7:54
@Damien_The_Unbeliever, yes you are correct. I was thinking you meant one column must be NULL. Sorry. My bad. — PaulFrancis
– PaulFrancis, Commented Aug 18, 2015 at 8:27

Zohar Peled · Accepted Answer · 2015-08-17 14:43:07Z

11

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);

answered Aug 17, 2015 at 14:43

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jamie Bull · Accepted Answer · 2015-08-17 16:47:25Z

2

What about CHECK Constraints?

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

edited Aug 17, 2015 at 16:47

Jamie Bull

13.6k18 gold badges80 silver badges123 bronze badges

answered Aug 17, 2015 at 14:43

Arsen Mkrtchyan

50.9k33 gold badges155 silver badges187 bronze badges

Collectives™ on Stack Overflow

SQL: Allow NULL on two columns but not both

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related