Oracle 强制column1或column2中的任何一个不为null

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');
复制

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          
   3    nzb      
复制