Sql Compare Including primary keys when contraints change
MartinButcher
Posts: 16
I have just upgrade to 5.2.0.32 hoping that it would fix a problem in 4.1 that I had but to no avail.
I have a production and development database and have changed some of the contraints on non key fields in some tables. When I do a compare between the 2 databases the sycronisation script wants to drop and create all the primary keys on the table where the constraints have changed. I have tried all the options but the only way I can get it to stop droping the primary key is if I get it to ignore check constraints which obviously I dont want to do.
I have a production and development database and have changed some of the contraints on non key fields in some tables. When I do a compare between the 2 databases the sycronisation script wants to drop and create all the primary keys on the table where the constraints have changed. I have tried all the options but the only way I can get it to stop droping the primary key is if I get it to ignore check constraints which obviously I dont want to do.
Comments
I would imagine that the issue is that SQL Compare needs to drop the primary key in order to change a check constraint on the same column. If that's the case, I don't think there is anything you can do about it, short of telling SQL Compare to not change check constraints.
Thanks for the reply but I am afraid that the constraint column I changed was not the same as the primary key column.
The column I changed just holds the status of the record and I wanted to allow a new status.
I manually edited the sycronisation script to remove all the drops of the primary key and it works fine. But it is a real pain having to do this as it affects 30 tables. The only way I can stop it including the drop of the primary keys is to set the options to ignore constraints which defeats the change I have made!
Some more info for you
This is the script generated for one of the tables. Notice it does not try to recreate the primary key. It also fails to delete the primary key as I have the 'Include dependancies' option turned off and it is being referenced by another table as a foreign key.
NB The primary key is on ClubID and WorkstationID
PRINT N'Dropping constraints from [dbo].[Workstation]'
GO
ALTER TABLE [dbo].[Workstation] DROP CONSTRAINT [CK__Workstati__Statu__375B2DB9]
GO
PRINT N'Dropping constraints from [dbo].[Workstation]'
GO
ALTER TABLE [dbo].[Workstation] DROP CONSTRAINT [CK__Workstati__TypeC__384F51F2]
GO
PRINT N'Dropping constraints from [dbo].[Workstation]'
GO
ALTER TABLE [dbo].[Workstation] DROP CONSTRAINT [PK__Workstation__3943762B]
GO
PRINT N'Adding constraints to [dbo].[Workstation]'
GO
ALTER TABLE [dbo].[Workstation] ADD CONSTRAINT [CK__Workstati__Statu__5E74FADA] CHECK (([Status]='IN' OR [Status]='AC'))
GO
ALTER TABLE [dbo].[Workstation] ADD CONSTRAINT [CK__Workstati__TypeC__5F691F13] CHECK (([TypeCode]='HO' OR [TypeCode]='PS'))
Tried your suggestion about setting ignore index and constraint names but it still wants to drop the primary keys. I also had ignore indexes set and it it tried to drop 3 indexes as well as the primary keys
By the way the create database script for the devolopment DB is created through Erwin Datamodeler which as you say auto names the constraints and keys
Indexes,
Constraint and Index Names
White Space
Comments
Users Permissions and role memberships
statistics
foreign keys
fill factor
filegroups
certificates
user properties
with element order
The only reason I can see why it would want to drop the primary keys / indexes is that the names are different between the 2 versions of the database.
The example I gave you above of the workstation has nothing else done to it in the syncronisation script.
But even if the key gets dropped because of the name I would expect it to get rebuilt with the new name which didnt get included in the script.
By the way I am running SQL 2005 service pack 1
Key names
Development = PK__Workstation__605D434C
Production = PK__Workstation__3943762B
When you check the Constraint and Index Names Option a message comes up saying:
'Ignore the names of indexes, foreign keys, primary keys, and defualt, unique, and check constraints when comparing databases. Note the names will not be ignored when the databases are syncronised.'
This seems to be the root of the problem. They dont show up in the compare but do in the syncronization script. However the script is not acting as I would expect. If it is going to try to drop a key because it has a differing name surly it should try to re-create it with the new name?
And surely it should drop all keys with differing names not just the ones in tables that are having their constraints or binding changed?
Sure, you can tell SQL Compare to ignore the constraint and index names, but it does indeed create them with the source name. That still does not mean to say that a difference in the name would cause the constraint to be flagged as a difference. So there is still a different underlying reason why the change is appearing in the script than a difference in the constraint name.
Thanks for the reply.
I have tested this extensivly and even gone to the bother of naming my primary keys explicitly. It seems that every time a constraint other than the primary key or index is changed, then SQL Compare will try to drop the key constraints as well as the specific constarint on the non key field that has changed. It only does this on the tables that have had a constraint changed, it ignores key name changes on other tables that have not had a non key constraint change.
However if I apply this script then make another non key constarint change on one of the unaffected tables then the product will try to drop the keys on this table that it ignored the first time.
This leaves me with closely checking and editing the script by hand to remove the unwanted code which defeats the purpose of the product.
I can't accurately reproduce the problem that you describe. Would it be possible to send SQL Compare schema snapshots to support@red-gate.com?
Thanks!
It seems the problem has gone away! (Picture me red faced here)
I edited my sql compare project to use the database snapshots I had just generated in response to you request. (I usually just use the actual dbs) and everything worked OK so I re-edited the project to use the actual dbs and afer taking several minutes to register my live production db the sycronisation worked as I expected again.
Could it be that the project got corrupted in some way?
Martin
I can't really say. You'd think if the SQL Compare project settings were corrupt, you'd more likely get an error loading the project!