Dual Column Advance Constraint. [message #376924] |
Fri, 19 December 2008 03:17  |
weekend79
Messages: 198 Registered: April 2005 Location: Islamabad
|
Senior Member |

|
|
HI
Oracle10g
I have a table Say Tab_A with two Columns Col1, Col2 I need a constraint that enforce following rules.
Both Col1 and Col2 can’t be null in same row.
Both Col1 and Col2 can’t be not null in same row.
Simply I require anyone of them null and other not null for every row and combination can’t be null.
Please advise how to establish this constraint.
Wishes
|
|
|
|
|
|
Re: Dual Column Advance Constraint. [message #377229 is a reply to message #377225] |
Mon, 22 December 2008 00:35  |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
I think this construct is easier to read than the previous one
SQL> create table null_cons (col1 number, col2 number);
Table created.
SQL> alter table null_cons
2 add constraint null_check
3 check ((col1 is null and col2 is not null)
4 or (col1 is not null and col2 is null))
5 ;
Table altered.
It says just what it checks. Anyone reading this will be able to see in a single instance what it is meant to check.
Prove it works:
SQL> insert into null_cons (col1, col2) values (1, 2);
insert into null_cons (col1, col2) values (1, 2)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.NULL_CHECK) violated
SQL> insert into null_cons (col1, col2) values (1, null);
1 row created.
SQL> insert into null_cons (col1, col2) values (null, 1);
1 row created.
SQL> insert into null_cons (col1, col2) values (null, null);
insert into null_cons (col1, col2) values (null, null)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.NULL_CHECK) violated
|
|
|