Renaming Primary Key Constraints

cwills

Hi,

I'm using the SQL Compare 8 to compare a scripted DB (old) against a live DB hosted in SQL Server (new).

Once I have compared the two databases I take the synchronization Script and run it against a third DB (also hosted in SQL Server) the schema of which is exactly the same as the scripted DB (old).

This works great for the most part. Except when the differences include a renamed primary key constraint. As it doesn't drop and re-create the dependent foreign key constraints.

For Example, when I compare the two live DB's where the only difference is the name of a primary key I get this script:

ALTER TABLE [dbo].[Order] DROP CONSTRAINT [FK_Order_Customer];

ALTER TABLE [dbo].[Customer] DROP CONSTRAINT [PK_CustomerKey];
ALTER TABLE [dbo].[Customer] ADD CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED  ([CustomerId]);

ALTER TABLE [dbo].[Order] ADD CONSTRAINT [FK_Order_Customer] FOREIGN KEY ([CustomerId]) REFERENCES [dbo].[Customer] ([CustomerId]);

However, when I compare the scripted DB against the Live DB I get only this part of the above script:

ALTER TABLE [dbo].[Customer] DROP CONSTRAINT [PK_CustomerKey];
ALTER TABLE [dbo].[Customer] ADD CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED  ([CustomerId]);

Which throws an error when run against a live DB:

The constraint 'PK_CustomerKey' is being referenced by table 'Order', foreign key constraint 'FK_Order_Customer'. Could not drop constraint.

Is there any way for me to 'trick' SQL Compare into thinking the scripted DB is a live DB so it will generate the correct SQL Script?

Thanks[/code]

Brian Donahue

Hi,

Comparing a scripts folder to a live database should be exactly the same as comparing two live databases. The problems that seem to be coming up a lot with scripts folders are when the scripts were not produced by SQL Compare. In that case, the files are read in alphabetical order and some DDL, for instance ALTERs where it expects CREATE can cause problems.

If the script was not made by SQL Compare, then it would be worth checking that the FK creation isn't happening in some other file that gets read before the file containing the DDL for the table it's based on.

cwills

Hi Brian,
Thanks for the reply.

The scripted DB I'm comparing was created using SQL Compare. The folder structure looks similar to this:

/Security
/Tables
/Tables/dbo.Customer.sql
/Tables/dbo.Order.sql
...
/Views
/SqlCompareDatabaseInfo.xml

Any other ideas on what may be causing this problem?
Perhaps its a bug with SQL Compare?

Brian Donahue

It's impossible to say why the migration is not coming out as expected without at least a copy of your script. You could try the latest cumulative patch and see if that helps.

http://www.red-gate.com/MessageBoard/vi ... php?t=9860