Blog

Learnomate Technologies > Blog > Oracle DBA > ORA-02438: Column check constraint cannot reference other columns

ORA-02438: Column check constraint cannot reference other columns

  • November 10, 2021
  • Posted by: Ankush Thavali
  • Category: Oracle DBA
No Comments

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.

Scenario 1: When you really are referring to other column in Create statement 

SQL> create table test_table
  2  (
  3    id      number(10),
  4    name    varchar2(200)  check (age >= 0),
  5    age     number(3)
  6  );
  name    varchar2(200)  check (age >= 0),
                                         *
ERROR at line 4:
ORA-02438: Column check constraint cannot reference other columns

Solution: Just Correct the statement 

SQL> create table test_table
  2  (
  3    id      number(10),
  4    name    varchar2(200),
  5    age     number(3) check (age >= 0)
  6  );

Table created.

Scenario 2: When you do a silly spelling mistake in Alter table statement

SQL> create table test_table
  2  (
  3    id      number(10),
  4    name    varchar2(200),
  5    age     number(3)
  6  );

Table created.

SQL> alter table test_table add contraint test_check check (age >=0);
alter table test_table add contraint test_check check (age >=0)
                                                              *
ERROR at line 1:
ORA-02438: Column check constraint cannot reference other columns

Solution: Just Correct the spelling 🙂

SQL> alter table test_table add constraint test_check check (age >=0);

Table altered.
ORA-02438