question

lavanyadeepak avatar image
0 Votes"
lavanyadeepak asked ErlandSommarskog commented

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

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-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
1 Vote"
Cathyji-msft answered

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

lavanyadeepak avatar image
0 Votes"
lavanyadeepak answered lavanyadeepak published

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

lavanyadeepak avatar image
0 Votes"
lavanyadeepak answered ErlandSommarskog commented

Thank you for clarification @ErlandSommarskog . 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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OK, that make sense.

Since OpenDBDiff is on GitHub, you could dig into it and submit a pull request to change the behaviour. There is a column *is_system_named*in sys.check_constraints that you can use. If you are not inclined to hack the code yourself, you could at least submit an issue about it and the maintainer could pick it up. There are similar columns in sys.foeign_keys and sys.default_constraints.

And, yes, explicitly named constraints is best practice, but if you have databases where this standard has not been respected (and they are not uncommon), that is not really going to help you.

1 Vote 1 ·