CODEWITHSUNDEEP
sql
Gambit2007
Gambit2007

Reputation: 4014

SQL - "linking" 2 Primary Keys to 2 different tables?

I have these 3 tables:

CREATE TABLE ORDERS
(ORDER_NUM CHAR(5) PRIMARY KEY,
FOREIGN KEY (CUSTOMER_NUM) REFERENCES CUSTOMER,
ORDER_DATE DATE,
CUSTOMER_NUM CHAR(3) );

CREATE TABLE PART
(PART_NUM CHAR(4) PRIMARY KEY,
DESCRIPTION CHAR(15),
ON_HAND DECIMAL(4,0) CHECK (ON_HAND >= 0),
CLASS CHAR(2),
WAREHOUSE CHAR(1),
PRICE DECIMAL(6,2) );

CREATE TABLE ORDER_LINE
(ORDER_NUM CHAR(5),
PART_NUM CHAR(4),
NUM_ORDERED DECIMAL(3,0) CHECK (NUM_ORDERED >= 0),
QUOTED_PRICE DECIMAL(6,2),
PRIMARY KEY (ORDER_NUM, PART_NUM));

As you can see, ORDER_LINE contains 2 Primary Keys - ORDER_NUM and PART_NUM, which should be linked to the other tables.

What would be the best way to link ORDER_NUM to the ORDER_NUM that's in the ORDERS table, and PART_NUM to PART_NUM that's in the PART table?

Would it just be adding:

PRIMARY KEY (ORDER_NUM, PART_NUM),
FOREIGN KEY (ORDER_NUM) REFERENCES ORDERS,
FOREIGN KEY (PART_NUM) REFERENCES PART);

to that last table?

Thanks!

Upvotes: 0

Views: 1235

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95771

As you can see, ORDER_LINE contains 2 Primary Keys - ORDER_NUM and PART_NUM, which should be linked to the other tables.

ORDER_LINE contains one primary key; that primary key consists of two columns.

Yes, you can just add this to ORDER_LINE.

PRIMARY KEY (ORDER_NUM, PART_NUM),
FOREIGN KEY (ORDER_NUM) REFERENCES ORDERS,
FOREIGN KEY (PART_NUM) REFERENCES PART);

Upvotes: 1

Related Questions