Home » SQL & PL/SQL » SQL & PL/SQL » Dual Column Advance Constraint. (Oracle10g, Win2003)
Dual Column Advance Constraint. [message #376924] Fri, 19 December 2008 03:17 Go to next message
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 #376931 is a reply to message #376924] Fri, 19 December 2008 03:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Just write it in SQL like you write it in english.

Regards
Michel
Re: Dual Column Advance Constraint. [message #377224 is a reply to message #376924] Sun, 21 December 2008 23:33 Go to previous messageGo to next message
danish_fsd@yahoo.com
Messages: 38
Registered: February 2008
Location: Pakistan
Member
hello

Try the following create table command. Hope it will help you.

create table Tab_A
(
col1 varchar2(50),
col2 varchar2(50),
constraint chk_null check (nvl(length(col1||col2),0) <> 0)
)

Regards
Danish
Re: Dual Column Advance Constraint. [message #377225 is a reply to message #376924] Mon, 22 December 2008 00:04 Go to previous messageGo to next message
karthick_arp@yahoo.com
Messages: 6
Registered: November 2006
Location: India
Junior Member

Use CASE statement and create your CHECK constraint. Example is shown below.

SQL> create table t(
  2             col1 integer,
  3             col2 integer)
  4  /

Table created.

SQL> alter table t add constraint chk check (
  2                     case when col1 is null and col2 is not null then 1
  3                          when col2 is null and col1 is not null then 1
  4                          when col1 is null and col2 is null then 0
  5                          when col1 is not null and col2 is not null then 0
  6                     end = 1)
  7  /

Table altered.

SQL> insert into t values(1,1)
  2  /
insert into t values(1,1)
*
ERROR at line 1:
ORA-02290: check constraint (SYSADM.CHK) violated


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


SQL> insert into t values(1,null)
  2  /

1 row created.

SQL> insert into t values(null,1)
  2  /

1 row created.
Re: Dual Column Advance Constraint. [message #377229 is a reply to message #377225] Mon, 22 December 2008 00:35 Go to previous message
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
Previous Topic: count numbers in for loop
Next Topic: How to get the usage details of column or table ?
Goto Forum:
  


Current Time: Mon Feb 10 10:00:59 CST 2025