composite keys

[technohead]

Hi,
im creating a table with composite keys, that is the order_id and stock_id are primary keys, how do i do this

CREATE TABLE GIFT_ORDER_ITEM
(
ORDER_ID NUMBER,
STOCK_ID NUMBER,
QUANTITY NUMBER
)

 

[DaPi]

something like
PRIMARY KEY (ORDER_ID NUMBER, STOCK_ID NUMBER)

 

[JMelvin]

Create the table then try two seperate alter commands:

Alter Table Gift_Order_Item
add primary key(order_id);

Alter Table Gift_Order_Item
add primary key(stock_id);

 

[sem]

JMelvin: table can have only ONE primary key, so the second statement obviously will produce an error.

The correct syntax is

Alter Table Gift_Order_Item
add primary key(order_id, stock_id);

Or in one-shot with creating table:


CREATE TABLE GIFT_ORDER_ITEM
(
ORDER_ID NUMBER,
STOCK_ID NUMBER,
QUANTITY NUMBER,
primary key (ORDER_ID,STOCK_ID))