CODEWITHSUNDEEP
databaseoracle-databaseconstraints
Shahzu Khan
Shahzu Khan

Reputation: 11

How to make a check constraint which references two columns?

I want to create a table Sp in Oracle with following query :

create table SP(Order_date date ,Dely_date date  check(Dely_date>Order_date));

But the problem is that oracle shows an error "Column check constraint cannot reference other columns"

But if I create this table without this condition check(Dely_date>Order_date) then table is created.

Tell me why check constraint can't work with two columns.

If I alter the SP table with this clause check(Dely_date>Order_date)
then my check constraint works properly. But at the time of creation check constraint can't work with above condition.

Upvotes: 1

Views: 756

Answers (2)

APC
APC

Reputation: 146219

In your first attempt you are trying to create an inline constraint. We are only allowed to reference the owning column in an inline constraint. Your ALTER TABLE statement succeeds because it creates a table level constraint, and table level constraints can reference more than one column.

This gives us the clue how to fix your code so you can create the check constraint in the CREATE TABLE statement:

create table SP(Order_date date
               ,Dely_date  date 
               ,constraint dates_ck check(Dely_date > Order_date)
);

Note the comma before the constraint keyword: this makes it a table level (out-of-line) constraint. Also, note that I have named the constraint dates_ck. It's always good practice to name constraints, but in this case it's mandatory: we cannot create an out-of-line check constraint without naming it. (Try it and it'll hurl the same ORA-02438 exception.)

Upvotes: 3

Littlefoot
Littlefoot

Reputation: 142705

That's how Oracle did it - inline check constraint can't reference another column.

Workaround? Create out-of-line constraint:

SQL> create table SP
  2    (Order_date date ,
  3     Dely_date  date,
  4     --
  5     constraint ch_date check (Dely_date > Order_date)
  6    );

Table created.

SQL>   insert into sp values (date '2020-03-03', date '2020-02-15');
  insert into sp values (date '2020-03-03', date '2020-02-15')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CH_DATE) violated


SQL>   insert into sp values (date '2020-03-03', date '2020-04-23');

1 row created.

SQL>

Upvotes: 3

Related Questions