How to disable primary key constraint programmatically?

Question

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).

Answer 1

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.

Answer 2

Relational tables without primary keys are a very bad thing. Each row has to be unique in some way. If none of the candidate keys are designated as primary, the whole row has to be unique.

I'm not sure why you have to drop a primary key constraint, but I would consider doing that without replacing it with one of the other candidate keys is a red flag that should be investigated.