How to disable primary key constraint programmatically?

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

From stackoverflow

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

  tomaszs : This seems to delete constraint. How to restore it?

  tomaszs : The problem is that I do not know if I have a primary key CLUSTERED or not. So I can not restore it in this way.

  marc_s : The primary key is clustered by default - so if you didn't change it, it's clustered.

  tomaszs : I can't guess it because I didn't designed this table.

  tomaszs : Ok, but how to do it programmatically?

  Quassnoi : Call the procedure, take the resultset, parse the result

  tomaszs : It looks like to be a messy solution for me.

  Quassnoi : And it sure is. Instead of doing it programmatically, you can figure out what the primary key is using Management Studio, it's a one time task.

  tomaszs : No, it's not one time task bacause I will issue this command from my application for many tables. So I would like to do it fully programmatically. If it would be one time task I would be doing it in Management Studio, but it is not.

  Quassnoi : Figuring out whether the PRIMARY KEY is CLUSTERED on not is a one time task. Then you can drop and create the PRIMARY KEY programmatically.

  Quassnoi : And sure, dropping and creating the PRIMARY KEY is messy. You could possibly rewrite you query so that is does not violate the PRIMARY KEY, but since you didn't ask for it, here is the solution you asked for.

• 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

  tomaszs : This is not a solution that suits my needs, because I don't know what is the configuration of my primary key. I only have it's name. So If I will drop it, I can not restore it later by adding it.

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

  tomaszs : You've pointed out why this solution is bad, so I decided to mark your answer as proper one. And further more I've asked another question about the solution that will do this job in right way I think:

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

  tomaszs : Rebuilding PKEY is not a cost for me, so I would like to go in this direction.

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

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