Referencing a composite primary key
composite-primary-key, foreign-keys, oracle, primary-key, sql
Solution
Foreign keys have to match the primary/unique key they reference column for column. Since the primary key of `BOOKING_REFERENCE` is (`REFERENCE_ID`, `CUST_ID`), that means that the foreign key from `BR_FLIGHT` to `BOOKING_REFERENCE` must consist of 2 columns also. That means you need to add `CUST_ID` to the `BR_FLIGHT` table - either that or your `BOOKING_REFERENCE` primary key is wrong and should just be (`REFERENCE_ID`).
That said, it doesn't make sense to have foreign keys defined in both directions as you do. The "child" table should reference the "parent" and not vice versa.
Problem
I have two tables, with each table having a composite primary key. One attribute is in both composite primary keys. How am i supposed to reference the common attribute?? Do i just reference it as a FK in both tables as below? The cust_id and flight_id below are each part of the composite key as well and reference primary keys in other tables. (Ignore the third attribute in the erd for the br_flight table as I choose to use a composite key in the end). ``` CREATE TABLE BOOKING_REFERENCE ( REFERENCE_ID NVARCHAR(10) NOT NULL, CUST_ID NUMBER(10)NOT NULL, STATUS NVARCHAR (1), NOT NULL, PRIMARY KEY(REFERENCE_ID, CUST_ID), FOREIGN KEY(REFERENCE_ID) REFERENCES BR_FLIGHT(REFERENCE_ID): FOREIGN KEY (CUST_ID) REFERENCES CUSTOMER(CUST_ID); CREATE TABLE BR_FLIGHT ( REFERENCE_ID NVARCHAR(10) NOT NULL , FLIGHT_ID NVARCHAR (10) NOT NULL, PRIMARY KEY(REFERENCE_ID, FLIGHT_ID), FOREIGN KEY (REFERENCE_ID) REFERENCES BOOKING_REFERENCE(REFERENCE_ID) FOREIGN KEY (FLIGHT_ID) REFERENCES FLIGHT(FLIGHT_ID) ); ``` Would the above sql work?? Thanks in advance and apologies for the shoddy diagram:)