Just an update, I've been looking at this all today and still not got anywhere. This is not so much a problem with Primary Keys (as the title suggests). Its more a problem with the foreign key, although I removed the constraint so its just like a regular column to try and investigate what is causing the problem.
I created 2 very simple test databases (TestDB1, TestDB2).
Then in each, I created a very simple table (TestTable).
The table is created like so:
CREATE TABLE [dbo].[TestTable](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nchar](10) COLLATE Latin1_General_CI_AS NULL,
[CustomerId] [int] NOT NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Now in my 2nd database I create exactly the same table but instead of having a CustomerId column I renamed it manually to ApplicantId, to simulate me renaming a foreign key (again, I removed the constraints for simplicity so its just like any other regular column), but no NULLs are allowed.
Run SQL Compare and attempt to sync from TestDB2 to TestDB1.
I would expect that SQLCompare tries to select data into a temporary table, drop the table, create the new table and select the data back into it. If the column is in the same location, then the select should select into all the new columns in the correct order.
However, SQLCompare gives me the following error:
The column [ApplicantId] on table [dbo].[TestTable] 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.
So ok, how do I go about changing the foreign key column name without adding a column and allow NULLs?
We have a build system, and everything is checked out from source safe. SQL compare then syncs from a scripts folder to the database. We need SQLCompare to work in one go without fiddling with data in the database, or creating extra migration scrips.
What baffles me is that this error seems to be displayed for any column rename that is peformed whilst it has a NON-NULL policy. Surely this is going to be a big problem.