How to change the primary key for already existing table?

[sajithamol]

How to change the primary key for already existing table?

[rickbray66]

How to change the primary key for already existing table?

You can do this with an "ALTER TABLE" command:

Suppose you had the following sample table with id as the primary key:

create table mytest (
  id int not null default 0,
  some_val in not null default 0,
  foo varchar(32),
  primary key (id));

Given the above table, the sample "ALTER TABLE" commands would be:

alter table mytest
  drop primary key;
alter table mytest
  add primary key(id,some_val);

Keep in mind, however, that the data in your existing table may affect your ability to effectively change the primary key, such as any NULL values in a field. Any fields you want to add to the primary key definition must be declared as "NOT NULL", so you may have to alter that column as well.


Rick

[JD07]

But if the primary key is referenced by foreign keys and has an index, constraints are lost by dropping it.