0

Can i add a new column to a table which already has a primary key such that the new column and existing primary key form a composite primary key ?

user2859809
  • 91
  • 1
  • 7
  • 1
    Yes, please take a look at this post: http://stackoverflow.com/questions/2310561/change-primary-key – Adrián Apr 28 '16 at 17:43

1 Answers1

0

You can't do this directly - you'll have to drop the existing primary key and create a new one. Note that primary key columns must not be null. E.g.:

-- Add the new column
-- Make sure it doesn't have any nulls in it
ALTER TABLE mytable ADD newcolumn NUMBER(10) NOT NULL DEFAULT 1;

-- Drop the primary key
ALTER TABLE mytable DROP PRIMARY KEY;

-- Add a new primary key
ALTER TABLE mytable ADD CONSTRAINT mytable_pk 
PRIMARY KEY (oldcolumn1, oldcolumn2, newcolumn);
Mureinik
  • 297,002
  • 52
  • 306
  • 350