CODEWITHSUNDEEP
sqloracle-databasecheck-constraints
Gary Holiday
Gary Holiday

Reputation: 3562

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

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?

Upvotes: 0

Views: 61

Answers (2)

Aleksej
Aleksej

Reputation: 22949

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

Upvotes: 3

Sanders the Softwarer
Sanders the Softwarer

Reputation: 2496

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

Upvotes: 1

Related Questions