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:)
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.
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.
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).
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.
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)
);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With