1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

sql xor check constraint, one of 3 has to be filled

Discussion in 'SQL PL/SQL' started by JohnDoe88, Feb 26, 2009.

  1. JohnDoe88

    JohnDoe88 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    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
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    hi if I have two columns the solution is simple
    Code (Text):

    CREATE TABLE xor_check (id1 NUMBER,
                            id2 NUMBER,
                      CONSTRAINT xor
                           CHECK (
                                    (id1 IS NOT NULL AND id2 IS NULL)
                                        OR
                                    (id1 IS NULL AND id2 IS NOT NULL)                          
                                 )
    );
    but it get's complex for more columns. But basically it boils down to converting XOR to AND-OR
     
  3. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    An intelligent use of decode may serve your purpose.

    Code (Text):
    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.
     
     
  4. JohnDoe88

    JohnDoe88 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    yeah, that sounds good :)
    thanks a lot!
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Or rather using NVL2 as follows (using the same example of simbly_dba)

    Code (Text):

    SQL>
    SQL> 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 (NVL2(COL1,1,0)+NVL2(COL2,1,0)+NVL2(COL3,1,0)=1));

    Table created.

    SQL>  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 (SCOTT.CHECK_NULL) violated


    SQL> 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 (SCOTT.CHECK_NULL) violated


    SQL> insert into test_table values (null,null,'1','ww');

    1 row created.

    SQL>