Oracle constraint so only one of several columns has a value

Suppose you had a users table that was generated with the following script:

1
2
3
4
5
create table users(
user_id number generated by default on null as identity,
home_phone varchar2(100) null,
work_phone varchar2(100) null
);

User’s must have either a work or home phone but not both. I’ve seen some constraints to represent this requirement written as follows:

1
2
3
4
5
alter table users add constraint users_ck1 check(
1=2
or (home_phone is not null and work_phone is null)
or (home_phone is null and work_phone is not null)
);

users_ck1 works fine but is open developer error when creating or modifying. For example, suppose cell_phone was added to the table. The script to modify the constraint to include the new column would be:

1
2
3
4
5
6
7
8
alter table users drop constraint users_ck1;

alter table users add constraint users_ck1 check(
1=2
or (home_phone is not null and work_phone is null and cell_phone is null)
or (home_phone is null and work_phone is not null and cell_phone is null)
or (home_phone is null and work_phone is null and cell_phone is not null)
);

A different approach that is easier to write and maintain is to use the decode function to apply a number (0 or 1) to represent if the column has a value or not.

1
2
3
4
5
6
7
8
9
10
alter table users drop constraint users_ck1;

-- Note: could use nvl2 instead of decode as well
-- nvl2(home_phone, 1, 0) + ....
alter table users add constraint users_ck1 check(
decode(home_phone, null, 0, 1) +
decode(work_phone, null, 0, 1)
= 1
)
;

Updating the constraint to handle a new column is very simple. Using the previous example, the following snippet shows how easy it is to add cell_phone to the constraint:

1
2
3
4
5
6
7
8
9
alter table users drop constraint users_ck1;

alter table users add constraint users_ck1 check(
decode(home_phone, null, 0, 1) +
decode(work_phone, null, 0, 1) +
decode(cell_phone, null, 0, 1)
= 1
)
;