Skip to Main Content
  • Questions
  • Enforce either of column1 or column2 to be not null

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, raj.

Asked: October 31, 2018 - 2:55 pm UTC

Last updated: November 06, 2018 - 2:14 am UTC

Version: oracle 12 standard

Viewed 1000+ times

You Asked

Hi,
I have a table where I need either of the column to be not null, meaning at any point of time only one of these column should have a value and the other should be null.

I tried below:
CREATE TABLE "BCKC"."TEST" 
   ( "ID" NUMBER(9,0), 
 "TEXT" VARCHAR2(7), 
 "ADR" VARCHAR2(4), 
  CONSTRAINT "L_COL_CHK" CHECK ( text IS NOT  NULL OR adr IS NOT NULL) ENABLE
   ) ;

INSERT INTO  test values(1,'er','nzb');
INSERT INTO test values(1,'er','nzb');


I am still able to insert values into both columns which is not my requirement.

I am thinking of trigger and also see that trigger might slow down the insertion.

Any help would be highly appreciated.

--Thanks




and Chris said...

So you're looking for an exclusive OR?

Your constraint checks that at least one is not null. To ensure only one is null you also need to test that the other is null:

CREATE TABLE TEST  ( 
  ID NUMBER(9,0), 
  TEXT VARCHAR2(7), 
  ADR VARCHAR2(4), 
  CONSTRAINT L_COL_CHK 
    CHECK ( 
      ( text IS NOT NULL AND adr IS NULL ) OR 
      ( text IS NULL AND adr IS NOT NULL )
    )
) ;

INSERT INTO test VALUES ( 1,'er','nzb' );

ORA-02290: check constraint (CHRIS.L_COL_CHK) violated

INSERT INTO test VALUES ( 2,'er',NULL );
INSERT INTO test VALUES ( 3,NULL,'nzb' );
INSERT INTO test VALUES ( 4,NULL,NULL );

ORA-02290: check constraint (CHRIS.L_COL_CHK) violated

select * from test;

ID   TEXT     ADR      
   2 er       <null>   
   3 <null>   nzb      

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

NVL2 alternative

Jonathan Taylor, November 01, 2018 - 1:36 pm UTC

Alternatively:-

NVL2(text,1,0) + NVL2(adr,1,0) = 1

This is a shorter (but less readable) way of doing this.
However, it is a lot shorter if the number of columns is 3, 4 or more.

SQL> CREATE TABLE TEST (
2 ID NUMBER(9,0),
3 TEXT VARCHAR2(7),
4 ADR VARCHAR2(4),
5 CONSTRAINT L_COL_CHK
6 CHECK (
7 NVL2(text,1,0) + NVL2(adr,1,0) = 1
8 )
9 ) ;

Table created


SQL> INSERT INTO test VALUES ( 1,'er','nzb' );
INSERT INTO test VALUES ( 1,'er','nzb' )

ORA-02290: check constraint (FN.L_COL_CHK) violated
SQL> INSERT INTO test VALUES ( 2,'er',NULL );

1 row inserted


SQL> INSERT INTO test VALUES ( 3,NULL,'nzb' );

1 row inserted


SQL> INSERT INTO test VALUES ( 4,NULL,NULL );
INSERT INTO test VALUES ( 4,NULL,NULL )

ORA-02290: check constraint (FN.L_COL_CHK) violated

SQL>
Chris Saxon
November 02, 2018 - 4:56 pm UTC

True, though I generally prefer readability

raj v, November 05, 2018 - 9:30 pm UTC

Thank you soo much TOM, I feel any of the check constraint or NVL function can be applied to my requirement.

You are awesome!
Connor McDonald
November 06, 2018 - 2:14 am UTC

glad we could help

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.