How to constrain a set of columns to all be NULL or all be NOT NULL

Question

Lets say I have a table like so,

id | first name | middle name | last name

Now, the name columns can be NULL, but if any of the three, for example, first name is NOT NULL, then all three must be NOT NULL.

How can I enforce this in Oracle?

You might have to use a trigger for that and write the check yourself, at least have it as plan B if you don't get a better answer from someone that know more about Oracle — rypskar, Jan 19 '18 at 07:32
Whilst you can write a check constraint for this, you might want to consider a different strategy - isolate these "must all have the same null-ness" columns into a *separate* table that doesn't allow nulls. Then your constraint is enforced by the *presence* or *absence* of a row. — Damien_The_Unbeliever, Jan 19 '18 at 07:34

score 3 · Accepted Answer · answered Jan 19 '18 at 08:22

You can write a check constraint with the logics you need. For example:

alter table yourTable
  add constraint check_not_null check
    (   
        first_name  is not null and
        middle_name is not null and
        last_name   is not null
      OR
        first_name  is null and
        middle_name is null and
        last_name   is null
    );

score 1 · Answer 2 · answered Jan 19 '18 at 07:37

1

Say,

alter table your_table 
  add constraint set_of_column_chk
  check (nvl2(first_name, 1, 0) + nvl2(middle_name, 1, 0) + nvl2(last_name, 1, 0) in (0, 3));

answered Jan 19 '18 at 07:37

Sanders the Softwarer

2,478
1
13
28

How to constrain a set of columns to all be NULL or all be NOT NULL

2 Answers2