CODEWITHSUNDEEP
sqloracle-databaseforeign-keyscomposite-key
Cathrine Rydning
Cathrine Rydning

Reputation: 27

Oracle: Trouble creating two composite keys

One of my tables has a composite key comprised of three foreign keys (order_no, product_code and product_size).

In the table where Product_size is the primary key I want to have a composite key consisting of Product_size and product_code. If I do make these a composite key, product_size alone will not be a PK and so I cannot reference it in the creation of the composite key comprised of the three foreign keys.

create table product_stock
(
Product_Code varchar2(6) constraint productcode_fk references product(Product_Code),
Product_Size char(1) constraint size_chk check (Product_Size in   
('L', 'l', 'M', 'm', 'S', 's' )),
Product_Unitprice number(7,2) not null,
Stock_Quantity number(4) not null,
constraint prodstock_comp primary key (product_code,product_size)
);


create table orderline
(
Order_No number(4) constraint orderno_fk references order_detail(Order_No),
Product_Code varchar2(6) constraint productcode2_fk references product(Product_Code),
Product_Size char(1) constraint productsize_fk references product_stock(product_size),
Product_Quantity number(4) not null,
constraint orderline_comp primary key (Order_No,Product_Code, Product_Size)
);

Is this even possible to do?

Upvotes: 0

Views: 157

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

product_size alone will not be a PK and so I cannot reference it

Imagine you have two rows in product_stock table that have the same product_size (and different product_code). Now imagine one of these rows (but not the other) is deleted.

  • Should that be restricted because there are orderline rows that reference it?
  • Or should it be allowed because there still exists the other product_stock row, that orderline rows also reference?

(Similar problems exist for ON DELETE CASCADE / SET NULL and also when UPDATE-ing the PK.)

To avoid these kinds of ambiguities, the DBMS won't let you create a foreign key unless you can uniquely identify parent row, meaning you must use the whole key after the REFERENCES clause of your FK.

That being said, you could create both...

FOREIGN KEY (product_code)
REFERENCES product (product_code)

...and...

FOREIGN KEY (product_code, product_size)
REFERENCES product_stock (product_code, product_size)

Although, if you have the latter, the former is probably redundant.

In fact, (since you already have the FK product_stock -> product), having both FKs would create a "diamond shaped" dependency. BTW, some DMBSes have restrictions on diamond-shaped FKs (MS SQL Server doesn't support cascading referential actions on them).

Upvotes: 3

Laurence
Laurence

Reputation: 10976

Depends on your specific DBMS, but you can probably define foreign keys on multiple columns with a separate constraint clause like so:

create table orderline (
  Order_No number(4) constraint orderno_fk references order_detail(Order_No),
  Product_Code varchar2(6) constraint productcode2_fk references product(Product_Code),
  Product_Size char(1) constraint productsize_fk references product_stock(product_size),
  Product_Quantity number(4) not null,
  constraint orderline_comp primary key (Order_No,Product_Code, Product_Size),
  constraint fk_product_stock foreign key (product_code, product_size) references product_stock (product_code, product_size)
);

Upvotes: 1

Related Questions