# Oracle constraint so only one of several columns has a value



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

```sql
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:

```sql
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:

```sql
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. 

```sql
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) +
  0 = 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:

```sql
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) +
  0 = 1
)
;  
```