I'm trying to create a primary key from 2 columns, but it doesn't work well

Question

I'm learning Oracle by myself.

Here's my code:

create table Schedule
(
    Schedule_SN number(10) primary key,
    ScreeningDate date not null,
    Price number(6) not null
);
    
create table Seat 
(
    Schedule_SN number(10) REFERENCES Schedule(Schedule_SN),
    Seat_SN varchar2(4) not null
);
    
create table Reservation
(
    Reservation_SN number(15) primary key,
    DCtype number(2) not null,
    DCamount number(7),
    PaymentMethod number(1) not null,
    TotalPrice number(7) not null,
    ReservationDate date not null
);
    
create table Reservation_details    ** I need help here **
(
    Reservation_SN number(15) REFERENCES Reservation(Reservation_SN),
    Schedule_SN number(10) REFERENCES Schedule(Schedule_SN),
    Seat_SN varchar2(10) REFERENCES Seat(Seat_SN),
    CONSTRAINT Reservation_detailesPK primary key (Reservation_SN, Schedule_SN)
);

Error messages:

Errors - ORA-02270: no matching unique or primary key for this column-list
02270. 00000 - "no matching unique or primary key for this column-list"
*Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement gives a column-list for which there is no matching unique or primary key constraint in the referenced table.
*Action: Find the correct column names using the ALL_CONS_COLUMNS catalog view

How can I make my 2 columns (Reservation_SN, Schedule_SN) into a primary key?

Answer 1

The problem is with seat_sn. You want child column in reservation_details to reference parent column in seat, but the parent column is not a primary or unique key. Actually, seat has no primary key; just make seat_sn the primay key of this table (if this fits your use case), and the rest should run fine:

create table seat (
    schedule_sn nmber(10) references schedule(schedule_sn),
    seat_sn varchar3(4) primary key
)

Demo on DB Fiddle