By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,515 Members | 1,682 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,515 IT Pros & Developers. It's quick & easy.

How to change the primary key for already existing table?

P: n/a
How to change the primary key for already existing table?
Jun 28 '07 #1
Share this Question
Share on Google+
2 Replies


rickbray66
P: 7
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

P: 1
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.