column check constraint cannot reference other columns in oracle database-CodePudding

I have been looking into this error for quite a while and read other articles on this but can't seem to find the answer on why it gives the error

column check constraint cannot reference other columns

CREATE TABLE reservations
(member_id CHAR(7)
CONSTRAINT res_memberid_pk PRIMARY KEY
CONSTRAINT res_memberid_fk REFERENCES members(member_id)
,date_sh DATE DEFAULT SYSDATE
CONSTRAINT res_datesh_ck CHECK(date_sh < TO_DATE ("23:00","hh24:mi"))
,terrain NUMBER(2)
CONSTRAINT res_ter_nn NOT NULL
,opponent_id CHAR(7)
CONSTRAINT res_opid_fk REFERENCES members(member_id)
);

CodePudding user response：

Double quotes signifies an identifier. Single quotes are for text literals.

You want:

TO_DATE ('23:00','hh24:mi')

and TO_DATE ('23:00','hh24:mi') will be the 23:00 on the 1st day of the current month (which may or may not be what you want). However, it would not work in a check constraint as it does not specify the year, month and day components and will throw:

ORA-02436: date or system variable wrongly specified in CHECK constraint

If you want to constrain it so that the time component of the date is less than 23:00 then:

CONSTRAINT res_datesh_ck CHECK(EXTRACT(HOUR FROM CAST(date_sh AS TIMESTAMP)) < 23)

db<>fiddle here

CodePudding user response：

Yes a constraint can reference another column, it is called a "FOREIGN KEY CONSTRAINT". There are plenty of examples of the web on the syntax - it's a bit different from what you have written... you need to define the column and then set a constraint on that column indicating it is a foreign key and references the other table. For example (simplified, just a members table and a reservations table with a foreign key constraint to members):

create table members (
    member_id                      number generated by default on null as identity 
                                   constraint members_member_id_pk primary key
)
;

create table reservations (
    reserveration_id               number generated by default on null as identity 
                                   constraint reservations_reserveration_pk primary key,
    member_id                      number
                                   constraint reservations_member_id_fk
                                   references members on delete cascade
)
;

Note that naming your primary key column "member_id" in a table called "reservations" is very confusing.