Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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)

);

enter image description here

Would the above sql work?? Thanks in advance and apologies for the shoddy diagram:)

like image 962
Daniel o Keeffe Avatar asked May 01 '13 18:05

Daniel o Keeffe


People also ask

Can we reference a composite key as foreign key?

A referential constraint must have a one-to-one relationship between referencing and referenced columns. In other words, if the primary key is a set of columns (a composite key), then the foreign key also must be a set of columns that corresponds to the composite key.

Can a primary key be a composite?

A primary key that is made by the combination of more than one attribute is known as a composite key. In other words we can say that: Composite key is a key which is the combination of more than one field or column of a given table. It may be a candidate key or primary key.

How do you reference a primary key in SQL?

SQL PRIMARY KEY on ALTER TABLE. ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName); Note: If you use ALTER TABLE to add a primary key, the primary key column(s) must have been declared to not contain NULL values (when the table was first created).


2 Answers

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.

like image 55
Tony Andrews Avatar answered Oct 05 '22 22:10

Tony Andrews


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)
);
like image 21
Milica Medic Kiralj Avatar answered Oct 05 '22 22:10

Milica Medic Kiralj