DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: XOR 3 columns, one has to be set

Hybrid View

  1. #1
    Join Date
    Feb 2009
    Posts
    3

    XOR 3 columns, one has to be set

    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. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    You need logic and you cannot include a query in a check constraint.

    Have you considered to use a trigger?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Feb 2009
    Posts
    3
    no, i found a solution

    decode works fine:

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

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by Tobiask View Post
    no, i found a solution

    decode works fine:

    Code:
    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?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Feb 2009
    Posts
    3
    Code:
    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.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Nicely done. Congrats!
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width