Primary key problem

[Alvin chew]

hi, i using code to add new primary key(cam_name) to table which already have existing primary key (cam_code), but somehow it give me ""Multiple primary key defined " error

i using following code to query

stmt.executeUpdate("alter table newcampaign add PRIMARY KEY (cam_name);");

should i drop the existing primary key then only add both primary key together ?

like following

stmt.executeUpdate("alter table newcampaign drop PRIMARY KEY ;
 
stmt.executeUpdate("alter table newcampaign add PRIMARY KEY (cam_code,cam_name);");

i found it tredious to do that, is there any better solution ? thank you very much for answering !

for another case, let say the table contains 3 primary key, and i want to drop only one of the primary key, what should i do ?
[ December 05, 2004: Message edited by: Alvin chew ]

[Jeanne Boyarsky]

Alvin,
Are you trying to add a column to the existing primary key or a new key entirely? A table can only have one primary key. You can add a unique constraint to any other column(s) though.

If you are trying to add a column to an existing primary key, some databases do not let you do this without dropping the table.

[Alvin chew]

Thanks,Jeanne Boyarsky

[Adeel Ansari]

Originally posted by Jeanne Boyarsky:
If you are trying to add a column to an existing primary key, some databases do not let you do this without dropping the table.

Its 'without dropping the table' or 'without dropping the key'. If 'table' then you must be talking about MS ACCESS.

[Jeanne Boyarsky]

Adeel,
That is correct. I was trying to keep my comments non-database specific.