CODEWITHSUNDEEP
oracle-databaseoracle11gprimary-keyalter-table
Sangathamilan Ravichandran
Sangathamilan Ravichandran

Reputation: 381

Oracle primary key with multiple column

I have seen the below create table statement :

    create table cruise_orders
 (cruise_order_id number, 
order_date date, 
constraint pk_or_id primary key (cruise_order_id,order_date));

so here the primary_key has two columns. But in case if I try the below statement and try to alter the table cruise_orders and add the column order_date to the primary key pk_or_id it throws an error saying "A table can have only one Primary key". So how does it works with the first statement ? and why it doesn't work with the second alter statement ?

create table cruise_orders
 (cruise_order_id number, 
order_date date, 
constraint pk_or_id primary key (cruise_order_id));

Upvotes: 0

Views: 824

Answers (1)

MT0
MT0

Reputation: 168232

If you want it so that the cruise_order_id must be unique and, separately, that the order_date must be unique then use two constraints:

CREATE TABLE cruise_orders(
  cruise_order_id NUMBER,
  order_date      DATE, 
  CONSTRAINT cruise_orders__or_id__pk PRIMARY KEY(cruise_order_id)
);

ALTER TABLE cruise_orders ADD CONSTRAINT cruise_orders__order_date__u UNIQUE(order_date);

If you really do want to have a single constraint with both columns the drop the first constraint and create a second with both columns:

CREATE TABLE cruise_orders(
  cruise_order_id NUMBER,
  order_date      DATE, 
  CONSTRAINT cruise_orders__or_id__pk PRIMARY KEY(cruise_order_id)
);

ALTER TABLE cruise_orders DROP CONSTRAINT cruise_orders__or_id__pk;

ALTER TABLE cruise_orders ADD CONSTRAINT cruise_orders__coi__od__pk
  PRIMARY KEY(cruise_order_id, order_date);

Then you will be able to have duplicate cruise_order_id values provided the order_date is different and vice-versa.

Upvotes: 1

Related Questions