0

Scenario
A table in SQL Server has two or more columns, but the original column with the primary key constraint is no longer needed. So now you want to write a script to drop the original column w/ a PK constraint and put the PK constraint on a different column.

In this example, the table is empty.

Problem

  • You can't drop the first column without first dropping the PK constraint.
  • And you can't drop the PK constraint in SQL Server without the exact name of it.
    (more info here)
  • ....But you don't know the automatically generated name of the PK constraint.

NOTE: If the table is not empty, see this solution:
SQL Server 2008 Script to Drop PK Constraint that has a System Generated Name
(In most cases, this is the best solution.)

Question
The above solution will work, but what is another way to script dropping a column with a PK constraint when you don't know the constraint's name in an empty table?

Community
  • 1
  • 1
AndrewRalon
  • 496
  • 1
  • 9
  • 24
  • You seem to have answered your question about the exact time that you posted the question. Does that mean there is no question? If so, what is the question? – Sean Lange Jan 19 '17 at 16:56

1 Answers1

0

Another strategy -- besides figuring out the system generated name of the PK constraint so you can drop it as described here -- is to drop the empty table and recreate it without the original column with the primary key constraint, instead putting it on the new column.

To drop the column with an unknown PK constraint name:

  1. Generate a script to drop the table and re-create it from scratch
  2. Remove the OriginalColumn column from the CREATE TABLE query
  3. Put the PK constraint on the NewColumn column in the script
  4. Run the script to drop and re-create it without the original column -- effectively dropping OriginalColumn and "moving" the PK constraint from OriginalColumn to NewColumn
  5. ???
  6. Profit!
Community
  • 1
  • 1
AndrewRalon
  • 496
  • 1
  • 9
  • 24
  • You seem to have answered your question about the exact time that you posted the question. Does that mean there is no question? If so, what is the question? – Sean Lange Jan 19 '17 at 16:56
  • @SeanLange Is there a better way? This was my workaround, yet I was curious if there were any other strategies. – AndrewRalon Jan 19 '17 at 20:01