none
Visual Studie: Sql Schema Compare does not map identical Primary Key (and Unique) Constraints RRS feed

  • Question

  • Hi,

    I use Visual Studio SQL Schema Compare to update a productive SQLServer DB based on the development DB.

    Due to some varying set-ups there are some tiny variations in my UserData-table definition that seem to make SQL Schema Compare see differences, where no differences should be:

    My development UserData-table had a different id for the primary key constraint, compared to the constaint [PK__UserData__3213E83FB9D0FFE4] of the productive table.

    I tried to remedy this, by simply renaming the primary key constraint in the development table.

    However the comparer still shows a difference between the "named" primary key constraint in the development db and the "unnamed" constraint in the productive db, which leads to errors in the DDL update script.

    Of course I could just ignore this tiny difference, however I would prefer to make the development db setup as close to productive setup as possible, in order to make future updates as simple as possible.

    How could I make the primary key in the development DB "unnamed"?

    Or eventually the other way around: How could I make the primary key in the productive DB "named"?

    Best regards

    Michael

    Friday, February 14, 2020 1:11 PM

Answers

  • If you run this query, you will spot the difference:

    SELECT * FROM sys.key_constraints  WHERE object_name)parent_object_id) = 'UserData'

    Look at the last column.

    As Tom said, you should always give you constraints explicit names.

    In the target databaes, you can use sp_rename to rename the constraint.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, February 14, 2020 10:33 PM

All replies

  • This is because your constraint is "unnamed". You should always name your constraints.  When they are unnamed, they get auto generated names which never match.  This can cause the deployment to drop and recreate the constraint every deployment.

    Friday, February 14, 2020 1:43 PM
    Answerer
  • Hello Tom,

    Thank you for your swift answer: However in SQL Studio both definitions look alike:

    Production

    and development

    (Sorry German localisation)

    I cannot spot any difference. Could I revert my development db to an "unnamed" constraint?

    Michael

    Friday, February 14, 2020 1:55 PM
  • If you run this query, you will spot the difference:

    SELECT * FROM sys.key_constraints  WHERE object_name)parent_object_id) = 'UserData'

    Look at the last column.

    As Tom said, you should always give you constraints explicit names.

    In the target databaes, you can use sp_rename to rename the constraint.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, February 14, 2020 10:33 PM
  • Perfect. I was able to solve my problem.

    I will update the productive database accordingly.

    Michael

    Sunday, February 16, 2020 7:29 PM
  • Hi Michael,

    Was your issue resolved?

    If the replies could help you, please mark the useful reply as answer to help other community members find the helpful reply quickly

    If not, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Amelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 17, 2020 8:35 AM