Sql-server – How to disable primary key constraint programmatically

sql serversql-server-2005tsql

I have a table with primary key in my MS SQL Server 2005 table. I would like to disable it. Now i get error:

Violation of PRIMARY KEY constraint 'PK_Name'. Cannot insert duplicate key in object 'dbo.Table'.

I would like this error not to occur and to work with PRIMARY KEY like with normal column without constraint and than restore this constraint after doing my changes to it. How to disable this constraint?

Query I want to execute while PRIMARY KEY constraint is disable is complex and changes values in primary key column. In some points of this query it hits the situation when I have duplicate values in primary key column. But at the end of my query I have all values unique.

I do not know much about this constraint because i'm not a designer of this table. I have it's name, but I don't now if it's clustered and so on (what is config of this column).

Best Answer

ALTER TABLE mytable DROP CONSTRAINT PK_Name

To reenable it:

ALTER TABLE mytable ADD CONSTRAINT PK_Name PRIMARY KEY /* CLUSTERED */ (pk_column)

Uncomment CLUSTERED if you want your PRIMARY KEY to be clustered (i. e. the table rows themselves are being ordered)

To figure out if the PRIMARY KEY is clustered on not, issue:

EXEC sp_help 'mytable'

and look in the 6th resultset returned.