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

10 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
  4. create table carti
    (CodCarte VARCHAR2(5) constraint C_PK primary key
    ,Titlu VARCHAR2(100) constraint C_TITLU_NN not null
    ,AnAparitie CHAR(4) constraint AN_AP_CHK
    check (REGEXP_LIKE(ExtAng,'[1-9]{4}'))
    ,CodCat NUMBER(2) constraint C_CAT_FK references categorii
    ,CodEditura NUMBER(2) constraint C_COD_FK references editura
    );

    ReplyDelete
  5. create table Employee(
    E_no varchar(10) not null primary key check(E_no like 'E%'), E_name varchar2(50) not null, DOB date not null, DOJ date not null check(DOJ>DOB), post char(1) not null check(post in 'M','A','D','C'));
    PLz help me.......
    it is giving an error msg.....
    column check costraints can't reference other columns

    ReplyDelete
  6. create table SALES_ORDER_44(ORDER_NO varchar2(6) PRIMARY KEY check(ORDER_NO like 'O%'),ORDER_DATE date,CLIENT_NO varchar2(6) references CLIENT_MASTER_44(CLIENT_NO),SALESMAN_NO varchar2(6) references SALESMAN_MASTER__44(SALESMAN_NO),DELIVERY_TYPE char(1) default('f'),BILL_Y_N char(1),DELIVERY_DATE date check (DELIVERY_DATE>ORDER_DATE),ORDER_STATUS varchar2(10));

    ReplyDelete