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

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 2

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 3

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.

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

The below Worked for me(you must be db_owner to perform this action)

--Declare a variable to get the contraints on the table and assign to a variable

DECLARE @PK_CONST_NAME AS varchar(100)<br>
SET @PK_CONST_NAME = (SELECT NAME FROM SYS.KEY_CONSTRAINTS WHERE OBJECT_NAME(PARENT_OBJECT_ID) = '[TABLENAME]')

--Variables to store the queries

SET @DropAlterQuery = ''<br>
SET @RecreateAlterQuery = ''

-- Contruct Query

SET @DropAlterQuery = CONCAT('ALTER TABLE [TABLENAME] DROP CONSTRAINT ',@PK_CONST_NAME)

-- Executing the query constructed for Droping the contraints

EXEC(@DropAlterQuery)

-- Alter Column Size (Now this statement works as contraints are dropped)

ALTER TABLE [TABLENAME]
ALTER COLUMN BATCH_ID VARCHAR(200) NOT NULL

-- Contruct Query

SET @RecreateAlterQuery = CONCAT(CONCAT('ALTER TABLE [TABLENAME] ADD CONSTRAINT ',@PK_CONST_NAME), ' PRIMARY KEY (<<PRIMARY KEY COLUMN1>>, <<PRIMARY KEY COLUMN2>>, <<PRIMARY KEY COLUMN2>>... So on)') 

-- Executing the query constructed for Recreate the dropped contraints

EXEC(@RecreateAlterQuery)