Reputation: 4014
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
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