Comparing Databases within the same Server always shows differences in Constraints. How to avoid this situation?

Deepak Vasudevan 96 Reputation points
2021-04-02T06:38:17.283+00:00

Here is my situation. I have a database named LDCRM. I have just taken a backup and restored as LDCRM_2.

Ideally both these databases should be identical in terms of all database objects. However when I use OpenDiff tool (https://github.com/OpenDBDiff/OpenDBDiff) I see the constraints alone showing as different in the tables between the databases.

My guess is that constraint names are auto-generated in the databases and hence causes this difference. Is there an option to avoid such auto-generation and give a fixed name for the constraints.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,630 questions
0 comments
Accepted answer
  1. CathyJi-MSFT 21,081 Reputation points Microsoft Vendor
    2021-04-02T07:05:05.257+00:00

    Hi @ lavanyadeepak

    > My guess is that constraint names are auto-generated in the databases and hence causes this difference. Is there an option to avoid such auto-generation and give a fixed name for the constraints.

    Yes, if constraint_name is not supplied, a system-generated name is assigned to the constraint. System generated constraint name can be different, so please explicitly provide names to constraint and use it later on. Refer to the blog Beware of the System Generated Constraint Name.

    If the response is helpful, please click "Accept Answer" and upvote it, thank you.

    1 person found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deepak Vasudevan 96 Reputation points
    2021-04-02T07:09:24.833+00:00

    Thank you @CathyJi-MSFT ,

    Sincere thanks for confirmation about the auto-generated naming of the constraints and benefits of giving them an explicit friendly name as outlined in the article https://www.databasejournal.com/features/mssql/article.php/1570801/Beware-of-the-System-Generated-Constraint-Name.htm

    0 comments
  2. Erland Sommarskog 100.8K Reputation points MVP
    2021-04-02T09:28:15.773+00:00

    It certainly best practice to name your constraints explicitly. Nevertheless, this sounds like a flaw in OpenDBDiff to me. I have never heard of that tool before, so I cannot comment on it.

    If you have Visual Studio installed, you can do Database Compare in Visual Studio, and this comparison is smart enough to avoid calling out differences because of system-generated names.

    I am not sure, but I think that you have to install SQL Server Data Tools, to get access to Database Compare, but SSDT is free.

    0 comments
  3. Deepak Vasudevan 96 Reputation points
    2021-04-03T03:12:52.35+00:00

    Thank you for clarification @Erland Sommarskog . I do use Visual Studio Database Compare on my desktop.

    However on development server and to compare between the databases from there to other servers in customer's DMZ network I need this OpenDBDiff because I do not have a full-fledged IDE installed and OpenDBDiff is a portable XCopy solution.