title: SQL Server : rename primary key
body: <p>I have a table <code>doc.MyTable</code> which I want to deprecate by renaming to <code>doc._MyTable</code>. I then want to create a new <code>doc.MyTable</code> with the same primary key that the old <code>doc.MyTable</code> had. The problem is that SQL Server says that primary key already exists. So that means I need to rename the old primary key too. </p>
  
  <p>I tried the following:</p>
  
  <pre><code>EXEC SP_RENAME 'doc.MyTable', '_MyTable'
  
  -- Method 1
  EXEC SP_RENAME 'PK_MyTable', 'PK__MyTable'
  
  -- Method 2
  ALTER TABLE [doc].[_MyTable] DROP CONSTRAINT [PK_MyTable]
  ALTER TABLE [doc].[_MyTable] ADD CONSTRAINT [PK__MyTable]
  PRIMARY KEY CLUSTERED
  (
      [document_id] ASC,
      [line_id] ASC,
      [sub_line_id] ASC
  )
  
  -- Create new table
  CREATE TABLE [doc].[MyTable] (
      ... columns
  CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED (
      ... key columns
  )
  ... extra conditions
  </code></pre>
  
  <p>Method 1 throws this error:</p>
  
  <blockquote>
    <p>No item by the name of 'PK_MyTable' could be found in the current database 'db_dev', given that @itemtype was input as '(null)'.</p>
  </blockquote>
  
  <p>While method 2 throws this:</p>
  
  <blockquote>
    <p>Violation of PRIMARY KEY constraint 'PK_MyTable'. Cannot insert duplicate key in object 'PK.MyTable'. <br>The duplicate key value is (10358930, 336000, 0).` </p>
  </blockquote>
  
  <p>When I try to create the new primary key for the new table.</p>
  
  <p>I'm only using one of the two "Methods" at a time. How do I fix the issue? </p>