CODEWITHSUNDEEP
sqlazureazure-sql-database
Mason240
Mason240

Reputation: 3034

Change primary key on SQL database

I have a database that I migrated from MySql using SQL Server Migration Assistant and it is now stored in Azure.

SSMA apparently generated a new primary key column, named ssma$rowid, for one of the tables. I am trying to change the PK back to Card_Key, but I am getting the following error:

An error was encountered while applying the changes.
An exception occurred while executing the Transact-SQL statement:
ALTER TABLE [carddb].[Cards] ALTER COLUMN [Card_Key] INT NOT NULL.
The index 'Card_Key' is dependent on column 'Card_Key'.
ALTER TABLE ALTER COLUMN Card_Key failed because one or more objects
access this column.

How can I make Card_Key the PK again?

Upvotes: 1

Views: 533

Answers (1)

Herve Roggero
Herve Roggero

Reputation: 5249

The easiest might be to create a new table [cards2] with the correct primary key and copy your data from [cards] into the new table (just run a INSERT INTO cards2 ... SELECT ... FROM cards). Once that's done, you can drop (or rename to [cardsold] be on the safe side) the original table [cards], and rename the new table as [cards]: sp_rename cards2, cards

This should work.

Upvotes: 1

Related Questions