ORA-02438: Column check constraint cannot reference other columns

ORA-02438: Column check constraint cannot reference other columns
Cause: attempted to define a column check constraint that references another column.
Action: define it as a table check constraint.

Lets check scenario:

Case 1: Missing Constraint Name

CREATE TABLE EMP_tab (
            EMPNO NUMBER (4,0) PRIMARY KEY,
            ENAME VARCHAR2 (20) NOT NULL,
            MGR NUMBER (4,0) REFERENCES EMP (EMPNO)
                  ON DELETE SET NULL,
            DOB DATE,
            HIREDATE DATE,
            EMAIL VARCHAR2 (50) UNIQUE,
            CONSTRAINT MY_Ch1 CHECK (HIREDATE >= DOB+365*16),
            CONSTRAINT  CHECK  ((INSTR  (EMAIL, '@') > 0 AND (INSTR(EMAIL,'.') > 0))
      ))

ORA-02438: Column check constraint cannot reference other columns


Here add explicit constraint name for last check constraint and issue will be fixed.

SQL> CREATE TABLE EMP_tab (
              EMPNO NUMBER (4,0) PRIMARY KEY,
              ENAME VARCHAR2 (20) NOT NULL,
              MGR NUMBER (4,0) REFERENCES EMP (EMPNO)
                    ON DELETE SET NULL,
              DOB DATE,
              HIREDATE DATE,
              EMAIL VARCHAR2 (50) UNIQUE,
              CONSTRAINT MY_Ch1 CHECK (HIREDATE >= DOB+365*16),
              CONSTRAINT  My_Ch2 CHECK  ((INSTR  (EMAIL, '@') > 0 AND (INSTR(EMAIL,'.') > 0))
        ));

Table created


Case 2: Specify Comma before adding constraints.

SQL> create table check_tab(txn_id integer,txn_amount NUMBER(20,2),Txn_date date check(txn_amount > 100));
create table check_tab(txn_id integer,txn_amount NUMBER(20,2),Txn_date date check(txn_amount > 100))

ORA-02438: Column check constraint cannot reference other columns

rewrite with comma as below

SQL> create table check_tab(txn_id integer,txn_amount NUMBER(20,2),Txn_date date, check(txn_amount > 100));

Table created