How to change primary key column in database and preserve da
I'm having trouble after some substantial re-factoring work to our database schema. I've had change the primary key column on about 10 tables. When I run the SQL Compare I get high warnings on 2 of those tables.
The warning is:
Title: The column [APPLICANTID] on table [dbo].[CU_G_PRIORITIES] must be added but has no default and does not allow NULL values. The table must be rebuilt. If the table contains data then the migration script will not work. To avoid this, add a default to the column or mark it as allowing NULL values.
I don't want to lose any data because its vital we preserve the data. I have other tables which have the primary key column name changed and the warning is only medium:
Title: Column [CUSTOMER_ID] on table [dbo].[CU_A_ADDITIONAL_OCCUPANTS] could not be matched to a column in the source table. The data in this column will be lost.
What is the resolution to this? I would have thought the migration script would select the data out of the table, drop the table, create a new one without constraints, re-insert the data and then re-apply constraints.
As long as the column hasn't changed order within the table it should be ok? The PK name was CUSTOMER_ID which has now changed to APPLICANTID