Home » SQL & PL/SQL » SQL & PL/SQL » xor check constraint
xor check constraint [message #388892] Thu, 26 February 2009 09:22 Go to next message
JohnnyDoe
Messages: 3
Registered: February 2009
Junior Member
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
Re: xor check constraint [message #388907 is a reply to message #388892] Thu, 26 February 2009 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just like you express it in english.
Post what you already tried.

Regards
Michel
Re: xor check constraint [message #389034 is a reply to message #388892] Fri, 27 February 2009 01:40 Go to previous messageGo to next message
JohnnyDoe
Messages: 3
Registered: February 2009
Junior Member
okay, at the moment i have this:

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.


does this work properly in all situations?
Re: xor check constraint [message #389036 is a reply to message #388892] Fri, 27 February 2009 01:43 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>does this work properly in all situations?
Depends upon your definition of "Properly".
If it means without error then you have a problem.
Please fix, & resubmit.
Re: xor check constraint [message #389040 is a reply to message #389036] Fri, 27 February 2009 01:46 Go to previous messageGo to next message
JohnnyDoe
Messages: 3
Registered: February 2009
Junior Member
BlackSwan wrote on Fri, 27 February 2009 01:43
>does this work properly in all situations?
Depends upon your definition of "Properly".
If it means without error then you have a problem.
Please fix, & resubmit.


i mean, does it resolve the problem?
the error messages are no problem, they should raise because the task was to deny more than one column filled...
Re: xor check constraint [message #389047 is a reply to message #389040] Fri, 27 February 2009 02:12 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If your requirement is at least and at most one column is not null, then this is what you wrote and it fits this.

Regards
Michel
Previous Topic: Issue with time stamp
Next Topic: Help in exists / not in
Goto Forum:
  


Current Time: Tue Dec 06 16:15:43 CST 2016

Total time taken to generate the page: 0.11258 seconds