Oracle SQL 12c How to correctly use compound foreign keys in compound primary keys? DISCLAIMER: I am a student. I do not work for an airline. I do not pretend to be a professional programmer.

Greetings, I want understand what I need to correct in order for these tables to work together. I'm sure it is obvious, but it isn't to me. To me, it looks like I'm matching my columns correctly. I'm sure it has to do with using a compound foreign key as part of my compound primary key - but I don't know how to correct this. My full code, with many 'TODOs' still remaining, is on Github Gist here.

I do not have access to an Oracle DBMS at this time, and won't for several days, so I'm using SQLFiddle; which doesn't give the full output SQLPlus or Developer could. It is also not optimal because I'm studying 12c, but SQLFiddle only offers 11g support. Thank you ahead of time.

This is SQL fiddle's error output:

ORA-02256: number of referencing columns must match referenced columns


And here are the two tables which don't agree with each other.

CREATE TABLE FLIGHT (
  FLIGHT_ID NUMBER(3),
  AIRCRAFT_ID VARCHAR(8),
  FLIGHT_DATE DATE,
  CONSTRAINT PK_flight_comp_pk PRIMARY KEY (FLIGHT_ID,FLIGHT_DATE),
  CONSTRAINT FK_flight_route_ID FOREIGN KEY (FLIGHT_ID)
    REFERENCES FLIGHT_ROUTE(ROUTE_ID),
  CONSTRAINT FK_flight_serial_ID FOREIGN KEY (AIRCRAFT_ID)
    REFERENCES AIRCRAFT(AIRCRAFT_ID)
);

CREATE TABLE RESERVATIONS (
  CUSTOMER_ID NUMBER(4),
  FLIGHT_ID NUMBER(3),
  QTY_RESERVED_SEATS NUMBER(3),
  CONSTRAINT PK_reservations PRIMARY KEY (CUSTOMER_ID,FLIGHT_ID),
  CONSTRAINT FK_reservations_customer_ID FOREIGN KEY (CUSTOMER_ID)
    REFERENCES CUSTOMER(CUSTOMER_ID),
  CONSTRAINT FK_reservations_flight_ID FOREIGN KEY (FLIGHT_ID)
    REFERENCES FLIGHT(FLIGHT_ID,FLIGHT_DATE)
);
ORA-02256: number of referencing columns must match referenced columns
  
  Cause: The number of columns in the foreign-key referencing list is not equal to the number of columns in the referenced list.
  Action: Make sure that the referencing columns match the referenced columns.


The problem is the foreign key should reference both columns used in the composite primary key.

Add the FLIGHT_DATE column to the RESERVATIONS table and change your code:

CONSTRAINT FK_reservations_flight_ID FOREIGN KEY (FLIGHT_ID)
    REFERENCES FLIGHT(FLIGHT_ID,FLIGHT_DATE)
);


To:

CONSTRAINT FK_reservations_flight_ID FOREIGN KEY (FLIGHT_ID,FLIGHT_DATE)
    REFERENCES FLIGHT(FLIGHT_ID,FLIGHT_DATE)
);