SQL: NOT NULL constraint on a set of columns

Question

Consider the following table:

CREATE TABLE user(
   first_name text,
   last_name text,
   ...
)

I would like to be able to add users with either a first name, a last name, or both - but I won't allow a user without any name. In other words (thanks, @wildplasser) - at least one column should not be NULL.

 INSERT INTO user(first_name, last_name) VALUES ("foo", NULL);  # 1. OK
 INSERT INTO user(first_name, last_name) VALUES ("foo", "bar"); # 2. OK
 INSERT INTO user(first_name, last_name) VALUES (NULL, "bar");  # 3. OK
 INSERT INTO user(first_name, last_name) VALUES (NULL, NULL);   # 4. NOT OK

Obviously, a NOT NULL constraint on each columns would disable examples 2 and 3.

How can I express a NOT NULL constraint on a set of columns in SQL?

One method is creating a trigger that check both columns, and if both columns are null, raise an exception. — NullEverything, Jan 16 '15 at 00:43
You mean: *at least* one of the columns should be BOT NULL ? — wildplasser, Jan 16 '15 at 00:44
would [this](http://www.postgresql.org/message-id/45E5627B.5090603@archonet.com) help ? — Abdelrahman Elkady, Jan 16 '15 at 00:46
[I meant: NOT NULL, obviously] Cannot be imposed by a column constraint , since that could only reference *one* column. So: you'll need a trigger, IMHO. — wildplasser, Jan 16 '15 at 00:58
I think this has already been answered here: http://stackoverflow.com/questions/21021102/not-null-constraint-over-a-set-of-columns?rq=1 — Turophile, Jan 16 '15 at 01:03

score 1 · Accepted Answer · answered Jan 16 '15 at 01:10

1

CREATE TABLE user(
   first_name text,
   last_name text,
   ...,
   CHECK (first_name IS NOT NULL OR last_name IS NOT NULL)
)

answered Jan 16 '15 at 01:10

gordy

9,360
1
31
43

I'm marking your answer as correct, and referring everybody to the deeper explanation here: http://stackoverflow.com/questions/21021102/not-null-constraint-over-a-set-of-columns?rq=1 – Adam Matan Jan 16 '15 at 01:28

score 0 · Answer 2 · edited Jan 16 '15 at 02:37

0

You can use query

For example like this

INSERT INTO user(first_name) VALUES (NULL);

The lastname will be NULL when the database schema set the column lastname allow NULL

Hopefully help Thanks

edited Jan 16 '15 at 02:37

sqluser

5,502
7
36
50

answered Jan 16 '15 at 00:45

masadi zainul

397
4
14

SQL: NOT NULL constraint on a set of columns

2 Answers2