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

You can only DROP, not disable a primary key or unique contraint. Then you'll have to add it again later.

You can disable foreign key and check constraints though.

ALTER TABLE foo DROP CONSTRAINT PK_foo
ALTER TABLE foo DROP CONSTRAINT UQ_foo_column


ALTER TABLE foo WITH CHECK
   ADD CONSTRAINT PK_foo PRIMARY KEY CLUSTERED (keycol1, ...)
ALTER TABLE foo WITH CHECK
   ADD CONSTRAINT UQ_foo_column UNIQUE (col1, ...)

Edit:

Based on your edit, you'll have to drop and recreate.

Edit again: sp_pkeys

Answer 3

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.

Answer 4

Do not violate PKEY constraint. IMHO it's the best solution, you will avoid cost of rebuilding a PKEY and what if you can't (duplicate remaining) ?

OR

Read the schema to know how to rebuild the PKEY constraint then use previouly posted solution.

Answer 5

It may be a better idea to SELECT your entire table into a temporary table, doing the transformation on the fly if possible, and then copying it back. And if you can't transform on the fly, it's a lot easier to add a simple integer row index as the primary key on the temporary table.

Answer 6

To find out what the primary key is (assuming your table is dbo.T1):

select si.name as name,  
(case  when (si.status & 16) > 0 then 1 else 0 end) as isclust,
si.keycnt as keycnt,
si.indid as indid
from sysindexes si
left join sysobjects so on so.id = si.id 
where  si.indid > 0 
and  si.indid < 255 
and so.xtype <> 'S'
and so.id = OBJECT_ID('dbo.T1')
and (si.status & 2048) > 0

This will give you something like:

name                                   isclust     keycnt indid
---------------------------------------------------------------
PK_T1                                      1           2      1

Here you have your primary key name (PK_T1), whether it's clustered or not, number of fields in it (2) and index id (you'll need it later).

Next run the following:

select INDEX_COL('dbo.T1', 1, 1) --returns Field1
select INDEX_COL('dbo.T1', 1, 2) --returns Field2

This will give you the names of two fields from the index. First parameter is your table name, second is index id obtained earlier and the third one loops from 1 to keycnt (returned on the previous step).

Having this information you should be able to reconstruct the primary key as follows:

ALTER TABLE dbo.T1 ADD CONSTRAINT PK_T1 PRIMARY KEY CLUSTERED (Field1, Field2)

Update: This might not be as accurate as parsing sp_help result mentioned earlier (you will miss the sort order and filegroup), but easier programmatically.