CODEWITHSUNDEEP
sqlsql-serverdatabase
Saurabh Garg
Saurabh Garg

Reputation: 201

Alter Primary key constraint

In my table I have primary key on 3 columns (name, dept, MobNo).

Now I want to change it to be on two columns (Name, MobNo).

Is there any way I can alter the primary key constraint without dropping it?

I know I can drop old constraint and can create new but without dropping old constraint it is possible to alter it?

Upvotes: 0

Views: 642

Answers (2)

iDevlop
iDevlop

Reputation: 25252

If you (probably) have dependencies on that PK, you will also have to drop them and recreate them. To have all this done automagically, it's easier from SSMS to right-click on the table, choose Design, and from there you click in the top left toolbar the button called Manage indexes and Keys. From there you make your changes, and at the end, you have 2 options:

  • you close and save your changes (and it works fine but you don't learn anything)
  • instead you click on the Generate change script so you can examine and execute the script later

(at least it works like this with my version 2014 of SSMS)

Upvotes: 0

Aizaz Ahmed
Aizaz Ahmed

Reputation: 210

The only and one way would be to drop the constraint with an Alter table then recreate it.

ALTER TABLE <Table_Name>
DROP CONSTRAINT <constraint_name>

ALTER TABLE <Table_Name>
ADD CONSTRAINT <constraint_name> PRIMARY KEY (<Column1>,<Column2>)

Upvotes: 1

Related Questions