467,887 Members | 1,594 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,887 developers. It's quick & easy.

How to change the primary key for already existing table?

How to change the primary key for already existing table?
Jun 28 '07 #1
  • viewed: 35485
Share:
2 Replies
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:


Expand|Select|Wrap|Line Numbers
  1. create table mytest (
  2.   id int not null default 0,
  3.   some_val in not null default 0,
  4.   foo varchar(32),
  5.   primary key (id));
  6.  
Given the above table, the sample "ALTER TABLE" commands would be:

Expand|Select|Wrap|Line Numbers
  1. alter table mytest
  2.   drop primary key;
  3. alter table mytest
  4.   add primary key(id,some_val);
  5.  
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
Jun 28 '07 #2
But if the primary key is referenced by foreign keys and has an index, constraints are lost by dropping it.
Mar 1 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by Mike Hubbard | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.