SQL drop table and re-create and keep data

I'm a bit late, just for reference.
If You are using SQL Server Management Studio, You could generate a DROP and RECREATE script with "Keep schema and data" option.

1. Right click on the desired DB in object explorer
2. Tasks > Generate scripts
3. Select the table you want to script
4. Then clicking on Advanced button
   • "Script DROP and CREATE" ->"Script DROP and CREATE"
   • "Types of data to script" -> "Schema and data"

Hope this helps

Using SQL Server Management Studio (SSMS), you can use it's table designer to specify the final condition of the table. Before saving the changes, have it generate the change script and save that script. Cancel out of the design window, open the script and review it. SSMS may already have generated a script that does everything you need, fixing the primary-foreign key relationship while preserving all existing data. If not, you will have a script, already started, that performs most of what you need to do and should be able to modify it for your needs.

This is your only solution.

Create the backup table, empty the original one, modify the table and then insert step-by-step until you find a violation.

Here's some pseudo-code. No need to make a backup table, just make a new table with the right constraint, insert your records into it, and rename.

CREATE TABLE MyTable_2
(...field definitions)

<add the constraint to MyTable_2>

INSERT INTO MyTable_2 (fields)
SELECT fields
FROM MyTable

DROP TABLE MyTable

exec sp_rename 'MyTable2', 'Mytable'