Referencing a composite primary key
Solution 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.
Solution 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)
);
Solution 3
You have to put the UNIQUE
restriction:
CREATE TABLE BOOKING_REFERENCE (
REFERENCE_ID NVARCHAR(10) NOT NULL UNIQUE,
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)
);
Daniel o Keeffe
22 year mcs_conversion comp science student based in Dublin Ireland. Interested in PHP and MYSQL in particular as well as CSS, HTML and JQuery. if you have any minor PHP or mysql projects available drop me an email and I'll work for the experience( if academia permits). Happy coding:)
Updated on September 15, 2021Comments
-
Daniel o Keeffe over 2 years
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:)
-
PinnyM about 11 yearsYou're going to need to use a tripod or faster shutter speed for that shot...
-