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.

Reference: http://docs.oracle.com/cd/E11882_01/server.112/e17766/e2100.htm

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.

Related Post:
- ORA-01439: column to be modified must be empty to change datatype
- ORA-00904: invalid identifier
- ORA-00001 unique constraint violated
- ORA-01489: result of string concatenation is too long
- ORA-00054: resource busy and acquire with NOWAIT specified

7 comments:

  1. which spelling in alter table statement????

    ReplyDelete
  2. Please help me

    CREATE TABLE RT_ITEM
    (
    ITEMNUM NUMBER(9) CONSTRAINT RT_ITEM_ITEMNUM_PK PRIMARY KEY,
    DESCRIPTION VARCHAR(50) NOT NULL,
    ONHAND NUMBER(9) NOT NULL,
    CATEGORYY CHAR(3) CONSTRAINT RT_ITEM_CATEGORYY_CK CHECK(CATEGORYY IN('GME','TOY','PZL')),
    STOREHOUSE NUMBER(5) NOT NULL,
    PRICE DECIMAL(5,2) CONSTRAINT RT_ITEM_PRICE_CK CHECK(PRICE>O)
    );



    Error report:
    SQL Error: ORA-02438: Column check constraint cannot reference other columns
    02438. 00000 - "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 constriant.

    ReplyDelete
    Replies
    1. you have miss-spelled 0 (Zero) to O (Alphabet O). Try following

      CREATE TABLE RT_ITEM
      (
      ITEMNUM NUMBER(9) CONSTRAINT RT_ITEM_ITEMNUM_PK PRIMARY KEY,
      DESCRIPTION VARCHAR(50) NOT NULL,
      ONHAND NUMBER(9) NOT NULL,
      CATEGORYY CHAR(3) CONSTRAINT RT_ITEM_CATEGORYY_CK CHECK(CATEGORYY IN('GME','TOY','PZL')),
      STOREHOUSE NUMBER(5) NOT NULL,
      PRICE DECIMAL(5,2) CONSTRAINT RT_ITEM_PRICE_CK CHECK(PRICE>0)
      );

      Delete
  3. Can you help me please in this
    CREATE TABLE Screening
    (
    screening_id NUMBER (6) NOT NULL check(screening_id>o) ,
    plan_id NUMBER (4) NOT NULL ,
    theatre_id NUMBER (1) NOT NULL ,
    screening_date DATE default sysdate NOT NULL unique ,
    screening_start_hh24 NUMBER (2) NOT NULL check(screening_start_hh24 between '09:00:00' and '22:00:00') unique,
    screening_start_mm60 NUMBER (2) NOT NULL check(screening_start_mm60 between '00:00' and '59:00') unique,
    plan_id_1 NUMBER (4) NOT NULL ,
    theatre_id_1 NUMBER (1) NOT NULL unique
    ) ;
    ALTER TABLE Screening ADD CONSTRAINT Screening_PK PRIMARY KEY ( screening_id ) ;

    Error report -
    SQL Error: ORA-02438: Column check constraint cannot reference other columns
    02438. 00000 - "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 constriant.

    Error starting at line : 48 in command -
    ALTER TABLE Screening ADD CONSTRAINT Screening_PK PRIMARY KEY ( screening_id )
    Error report -
    SQL Error: ORA-00942: table or view does not exist
    00942. 00000 - "table or view does not exist"
    *Cause:
    *Action:

    ReplyDelete
    Replies
    1. For ORA-02438: Column check constraint cannot reference other columns
      use table level constraints instead of column level constraints

      Delete