Referencing a composite primary key

Question

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:)

Answer 1

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.

Answer 2

When you reference composite primary key with a foreign key you must reference the whole key. In your case you should alter the BR_FLIGHT table and add the CUST_ID column

 ALTER TABLE BR_FLIGHT 
  
    ADD
       (
        CUST_ID NUMBER(10)NOT NULL
       );

And reference the full key as:

FOREIGN KEY  (REFERENCE_ID, CUST_ID) REFERENCES BOOKING_REFERENCE (REFERENCE_ID, CUST_ID)

Now DDL for BR_FLIGHT table will be:

CREATE TABLE BR_FLIGHT (
REFERENCE_ID NVARCHAR(10) NOT NULL ,
CUST_ID NUMBER(10)NOT NULL,
FLIGHT_ID NVARCHAR (10) NOT NULL,
PRIMARY KEY(REFERENCE_ID, FLIGHT_ID),
FOREIGN KEY  (REFERENCE_ID, CUST_ID) REFERENCES BOOKING_REFERENCE (REFERENCE_ID, CUST_ID)
);

As Tony Andrews pointed out you don't need the foreign part in the BOOKING_REFERENCE table. It should look like this:

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)
);