Referencing a composite primary key
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:)
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.