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?
2 Answers
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
1 Comment
marc_s
This works as long as no other tables reference
dbo.branch. If you have tables referencing it (via a foreign key), then you must first drop all the referencing foreign keys, drop the primary key and re-create it, and then re-create all those referencing foreign keys.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.
Indexis the only possible way