CODEWITHSUNDEEP
sqlsql-server
Jason Clark
Jason Clark

Reputation: 1425

How to alter primary key column in SQL Server using T-SQL script?

Is there a way to remove a column from primary key? I am not talking about Alter a primary key constraint. I just want to free a column from primary key using T-SQL script. I can do it by modifying the column by right click on column name. but can't do the same task using T-SQL Script. Is there any idea how to accomplish this task?

Upvotes: 0

Views: 3667

Answers (2)

Krishna_K_Batham
Krishna_K_Batham

Reputation: 310

You can accomplish this using T-SQL. Let's say your table name is branch.

So first we will get primary key constraint name using following query:

Query

SELECT name
FROM sys.key_constraints
WHERE [type] = 'PK'
  AND [parent_object_id] = Object_id('dbo.branch');

Result

'PK_Branch'

Now we use that result in another query to drop primary key. This query will remove primary key from branch table.

Query

ALTER TABLE dbo.branch
DROP CONSTRAINT PK_Branch

Upvotes: 0

Jacky
Jacky

Reputation: 3239

Drop and Recreate the Index is the only way to do, even with Management Studio.

From Management Studio, right click a table, choose Design and see the Interface. After make some changes, before Saves your changes, right click on table Design and choose "Generate Change Script..." you will see what Management Studio do, "behind the scenes". It's actually drop table, re-create it with new Changes and add in data back to the table.

Upvotes: 1

Related Questions