October 10, 2012 at 7:54 am
Hi,
I have a table on which the primary key is defined as composite of two keys. Now i want to add one more column to the list of composite key. I have search on internet for the above mention scenario. I found out the following things
1. First i had to drop the constraint on these 2 composite keys by using alter table script.
2. I need to add the constraint for all the 3 columns using alter table script.
Is this the right approach? Isn't there any alter script which would help me to find directly add the constraint instead of dropping it first and then re-creating them.
October 10, 2012 at 7:56 am
You must drop the existing constraint and create the new constraint. Also, if the PK is the clustering key as well, you should drop or disable any non-clustered indexes before dropping the PK, then recreate or rebuild them.
Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
October 10, 2012 at 8:01 am
You need to drop and create it again.
October 10, 2012 at 8:57 am
Roland Alexander STL (10/10/2012)
You must drop the existing constraint and create the new constraint. Also, if the PK is the clustering key as well, you should drop or disable any non-clustered indexes before dropping the PK, then recreate or rebuild them.
Hi Roland,
Above you mention that i should drop or disable any non-clustered indexes before dropping PK, but just for instance i did not do that and drop the PK Constraint and then again recreated it with 3 set of column and it work. I would like to know the reason why did you mention it.
October 10, 2012 at 9:01 am
Shadab Shah (10/10/2012)
Roland Alexander STL (10/10/2012)
You must drop the existing constraint and create the new constraint. Also, if the PK is the clustering key as well, you should drop or disable any non-clustered indexes before dropping the PK, then recreate or rebuild them.Hi Roland,
Above you mention that i should drop or disable any non-clustered indexes before dropping PK, but just for instance i did not do that and drop the PK Constraint and then again recreated it with 3 set of column and it work. I would like to know the reason why did you mention it.
If the table is clustered on the PK, then the PK is also the lookup key for any nonclustered indexes. If that is the case, and you drop the PK without dropping the NC indexes, then the table will (after dropping the PK) be a heap, and each index will be rebuilt using the RID (Row ID) as the lookup key. Then when you create the new PK, the NC indexes will be rebuilt again, so that they can use the new PK as the lookup. If you disable or drop the NC indexes first, you only have to rebuild them once.
Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
October 10, 2012 at 4:00 pm
Roland,
You are saying this for efficiency sake not to avoide doing something that will fail right? The rebuild w/o dropping will work, with a small data collection it really does not mater but with larger tables it will.
Is that what you were getting at?
TIA
m.
Not all gray hairs are Dinosaurs!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply