Oracle 12c - Column check constraint cannot reference other column

Question

I have to modify my table and I try do it as following:

ALTER TABLE projects
    MODIFY (
        id_proj NUMBER(4) CONSTRAINT pk_proj PRIMARY KEY,
        desc VARCHAR2(40) NOT NULL CONSTRAINT uk_proj UNIQUE,
        end_date CONSTRAINT chk_date CHECK(end_date > start_date),
        fund CHECK (fund > 0)
    );

And when I try execute this query I get an 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 constraint.

Additionally I want the column to accept values greater than 0 or NULL values.

Gordon Linoff Gordon Linoff · Accepted Answer · 2017-06-24T20:17:39

This is just a syntax quirk. Do this:

ALTER TABLE projects
    MODIFY (
        id_proj NUMBER(4) CONSTRAINT pk_proj PRIMARY KEY,
        desc VARCHAR2(40) NOT NULL CONSTRAINT uk_proj UNIQUE,
        end_date DATE,  -- I'm guessing this is the type
        CONSTRAINT chk_date CHECK (end_date > start_date),
        fund CHECK (fund > 0)
    );

As the error suggests, you cannot have an in-line check constraint that references other columns. You can still have a check constraint; it just needs to be declared by itself as a constraint.

While we are commenting:

desc is a really bad name for a column, because it is a SQL key word. Use descr or spell the whole thing out, description.
CONSTRAINT uk_proj UNIQUE seems verbose to me. It can be replaced with a simple UNIQUE. Admittedly, this doesn't allow you to name unique constraints. Is that important for your database?
The same is true of the primary key constraint. (If you have a reason to name them, then leave it as it is; I just don't often find such a reason.)

Oracle 12c - Column check constraint cannot reference other column

1 Answers