Sql Compare Including primary keys when contraints change

MartinButcherMartinButcher Posts: 16
edited August 15, 2006 1:01PM in SQL Compare Previous Versions
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.

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Martin,

    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.
  • Hi Brian

    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!
  • Hi Brian

    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'))
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Matybe the constraint is getting dropped because of a change in the constraint name. It looks like all of your constraints were 'auto-named' by Enterprise Manager. I'll bet setting the 'ignore names of constraints and indexes' option in SQL Compare will stop it from dropping the PK.
  • Hi Brian

    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
  • I currently have the following options set
    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
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Is there anything in the synch wizard's warning to indicate why the primary keys need to be dropped? Do tables need to be rebuilt, for instance?
  • Hi Brian

    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
  • I think I have found a partial reason for my problem though the behavior of the product is still slightly illogical.
    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?
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Martin,

    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.
  • Hi Brian

    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.
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello Martin,

    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!
  • Hi Brian

    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
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi,

    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!
Sign In or Register to comment.