Home » SQL & PL/SQL » SQL & PL/SQL » check constraint involving two column values
check constraint involving two column values [message #270630] Thu, 27 September 2007 12:37 Go to next message
RyjuRaju
Messages: 48
Registered: September 2007
Member
Hi
can anyone help me to get a check constraint that allows a column to be null only if an another column value matches with a specific value.

I have to use this constraint in developing a table with jdeveloper
many thanks
Ryju
Re: check constraint involving two column values [message #270636 is a reply to message #270630] Thu, 27 September 2007 12:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table t (a integer, b integer,
  2                  constraint chk check (a is not null or (a is null and b = 0))
  3                 );

Table created.

SQL> insert into t values (1,1);

1 row created.

SQL> insert into t values (null,0);

1 row created.

SQL> insert into t values (null,1);
insert into t values (null,1)
*
ERROR at line 1:
ORA-02290: check constraint (MICHEL.CHK) violated

Regards
Michel
Re: check constraint involving two column values [message #270641 is a reply to message #270630] Thu, 27 September 2007 12:55 Go to previous message
RyjuRaju
Messages: 48
Registered: September 2007
Member
Thank you very much
Previous Topic: Unable to send mail thru PL/SQL
Next Topic: Interesting SQL Nesting query
Goto Forum:
  


Current Time: Sat Dec 03 20:30:26 CST 2016

Total time taken to generate the page: 0.07632 seconds