XOR 3 columns, one has to be set

[Tobiask]

Hey there,

I have to check via constraints if one of three references is set.
For example: I have three columns, id1, id2 and id3.
One of this 3 columns have to be filled with an id. The other two columns have to be null (like xor).
Any idea how I can check this via a check constraint?

Greets,
John

[PAVB]

You need logic and you cannot include a query in a check constraint.

Have you considered to use a trigger?

[Tobiask]

no, i found a solution

decode works fine:

decode(col1,null,0,1)+decode(col2,null,0,1)+decode(col3,null,0,1)=1

[PAVB]

no, i found a solution

decode works fine:

decode(col1,null,0,1)+decode(col2,null,0,1)+decode(col3,null,0,1)=1

Great!
Would you mind in showing us how you include that in a check constraint?

[Tobiask]

create table test_table
  2  (col1 varchar2(10),
  3  col2 varchar2(10),
  4  col3 varchar2(10),
  5  some_val varchar2(10),
  6  constraint check_null
  7  check (decode(col1,null,0,1)+decode(col2,null,0,1)+decode(col3,null,0,1)=1)
  8  );
Table created.
insert into test_table values (null,null,null,'ww');
insert into test_table values (null,null,null,'ww')
*
ERROR at line 1:
ORA-02290: check constraint (FLIRT.CHECK_NULL) violated
 
insert into test_table values (null,'1','dsdsd','ww');
insert into test_table values (null,'1','dsdsd','ww')
*
ERROR at line 1:
ORA-02290: check constraint (FLIRT.CHECK_NULL) violated
 
insert into test_table values (null,null,'1','ww');
1 row created.

[PAVB]

Nicely done. Congrats!