SQL Server Performance Forum – Threads Archive

Primary Key

Hi Guys,
Is there any way to add columns to a primary key through query analyzer [?]. I searched thru the net and could find none. If you find a method do post it since I have got around 280 tables for which I need to add 2 fields for each and every table and have to include those two fields to the existing PK. Guys I am in the 11th hour and have to find out a solution for this. Hope I could get a solution ASAP.
Bye,
Raja.

HI ya, I don’t think this is trivial… you’d have to drop all foreign key constraints that refer to the primary keys
then drop all primary key constraints
then add all the new primary key constraints
then add the new columns to the foreign tables
then add the foreign key constraints All do-able but quite a bit of work. Check out ALTER TABLE to drop and add the constraints… You might also be able to create a separate new database with the structure you want and then migrate the data into it? Cheers
Twan

Are these two columns going to have the same name and datatypes for all your tables? Raulie

Thank you people for your prompt replies. TWAN, My lead does not accept to the solution which you had offered(Mind you I said as If I had found that solution[<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />]…). He says if it is possible in enterprise manager to easily change it, it should be possible in query analyser also. Raulie, You hit the bulls eye man…all the tables would have the same two column and datatype…Hope you give me an answer soon. Thanks once again guys, Raja.

EM does pretty much what I’d said… drops the foreign keys drops the primary key renames the old table creates a new table with the new columns copies the data adds the new primary key recreate the foreign keys it just does it behind the scenes, so if you want to do it in QA then you’ll have to do the same thing. The problem will come in to play when the foreign keys now no longer reference a valid primary key… This is valid in MSSQL, but is not good practice… a foreign key should always refer to the full primary key Perhaps you should ask your developer to perform this task on a copy of the database, that might persuade him to not suggest adding columns to an established datamodel…? <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> Cheers Twan

You can also create a script using a cursor to cycle through all your tables performing the tasks that Twan mentioned.
Raulie

Here is a nice little script to get you started. http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=4275
Raulie

Show your LEAD how easy it is in Enterprise Manager by running the Profiler to show all that EM is doing. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

I dont think it’s that easy to perform this task in EM on 280 tables.
Raulie
Hewlett-Packard Company

]]>