title: How can I replace the existing primary key with a new primary key on my table?
body: <p>I'm working with a legacy SQL Server database which has a core table with a bad primary key.</p>
  
  <p>The key is of type <code>NVARCHAR(50)</code> and contains an application-generated string based on various things in the table.  For obvious reasons, I'd like to replace this key with an auto-incrementing (identity) <code>INT</code> column.</p>
  
  <p>This is a huge database and we're upgrading it piece-by-piece. We want to minimize the changes to tables that other components write to. I figured I could change the table without breaking anything by just:</p>
  
  <ol>
  <li>Adding the new Id column to the table and making it nullable</li>
  <li>Filling it with unique integers and making it <code>NOT NULL</code></li>
  <li>Dropping the existing primary key while ensuring there's a uniqueness constraint still on that column</li>
  <li>Setting the new Id column to be the new primary key and identity</li>
  </ol>
  
  <p>Item 3 is proving very painful.  Because this is a core table, there are a <em>lot</em> of other tables with foreign key constraints on it.  To drop the existing primary key, it seems I have to delete all these foreign key constraints and create them again afterwards.</p>
  
  <p><strong>Is there an easier way to do this or will I just have to script everything?</strong></p>