• SQL Compare
• Database Builds and Deployments
• Schema Comparison Options

Handling System-named Constraints in SQL Compare

Although you can name keys and constraints in tables, it’s not compulsory. When you create a table build-script and use a shorthand version of the code for a constraint without giving it a name, then SQL Server shrugs and makes up a name, different every time. If you drop and re-create constraints when you import data, they get a new name.

Few people bother to give names to DEFAULT, UNIQUE, and CHECK constraints when they are creating or altering tables. Table scripts can get cluttered if you are fully using these excellent devices with explicit, permanent names. Even PRIMARY KEY or FOREIGN KEY constraints aren’t always named, and SQL Syntax allows this. There is nothing particularly wrong about all this because you don’t need to reference constraints by name, in normal use.

System-generated names can cause extra difficulties though, in certain circumstances. You may find an example of this when comparing databases using SQL Compare or SQL Change Automation. Unless specifically instructed, via an option, both these tools will assign these ever-changing system-generated names to constraints or keys that don’t have a used-defined name.

To demonstrate this, Let’s do a quick experiment to demonstrate why the phrase I used, ‘ever-changing’, will ring alarm-bells among seasoned database developers.

Identifying system-named constraints

System-named constraints ‘aren’t a SQL Code smell, though they may catch some people unawares. You can find out if your database has system-named constraints in it.

This code gives, with the database we are about to use as a test:

You will notice that the system-generated constraint names follow a naming convention that provides enough information to help with support if there is an error that mentions the name. The first two characters explain the type of constraint, followed by the table with which it is associated, and then, for a column-level constraint, the leftmost characters of the column name. In short, there is always enough information to find the offending constraint.

How SQL Compare treats system-named constraints by default

First, we’ll create a little database that uses system-named constraints: We’ll use the old Pubs build script. From it we’ll show two tables, publishers and titles, just to illustrate (I’ve marked the system-named constraint with comments):

We can run the complete build script and then use Tasks > Generate Scripts in SSMS to generate a new build script from that database. We’ll find that SSMS obligingly continues to use the shorthand “nameless” SQL syntax, in the build script it generates, for any constraint that didn’t have a user-defined name. If you want it to generate scripts that include the system-generated names, you must switch on the ‘include system constraint names’ option.

We’ll now use SQL Compare to generate a build script from this same copy of the original Pubs database, by comparing it to an empty target database, using all the default Compare options. In the Publishers table, the DEFAULT for the Country column suddenly has a name:

The CHECK constraint on Pub_ID in the Publishers table becomes this…

So, we can see that the Publishers table has suddenly sprouted named defaults and keys. The same is true of the Titles table.

And later, it gets its FOREIGN KEY constraint added so we can see what has happened to the name.

SQL Compare has turned all the system-named objects into user-named objects, using the system-generated name. It has, effectively, misrepresented the database by applying names to system-named objects. It doesn’t have to do it this way, but it is the default ‘Redgate’ option.

If you are always developing your databases by simply altering existing objects, this is unlikely to matter. However, by using the default options, you are storing up trouble when you subsequently compare tables to see if anything has changed. You also limit the value of any scripts generated by SQL Compare. I’ll illustrate some of this.

Same database, same script, same server

Let’s try another experiment. This time, we’ll create a new copy of the Pubs database using the same original Pubs build script, on the same server. We then compare the two databases with SQL Compare. Surprisingly, it finds them to be the same because SQL Server used the same algorithm to generate the system names, in both databases when it executed the table DDL code.

Same database, same script, different server

We now create an identical database using the same original Pubs build script, but on another server. This time it all goes wrong. All we have done is to use a different server. The two databases are the same because they were generated by the same script, but SQL Compare thinks the tables are different.

We can see why SQL Compare thinks they are different. When it generated the internal models of the two databases, it used the different system-generated names, as if they were actual user-provided names. Had we used the named syntax for both, then it would have worked but then you’d have to enforce a naming convention for all constraints for further database work.

Same database, different script, same server

Well, surely, we can just develop on the same server, then? Not necessarily. We can show this with another test. We build Pubs using the original script build, generate a build script from it using SSMS, as described earlier, and then use it to create a new build of the database called PubsTest.

Finally, we use SQL Compare to compare the two databases, Pubs and PubsTest:

Once again SQL Compare thinks that the tables are different even though they shouldn’t be.

Problems with allowing SQL Compare to give names to unnamed constraints

If you use SQL Compare with its default options to check on table differences, you’re going to get a lot of false positives. Tables will be flagged as being different when the only difference is the automatic system names of constraints. If two developers work from scripts to occasionally build tables while developing them, even if they are effectively the same, they will be flagged up as different in version control because this is a string-based comparison, not a semantic comparison.

SQL Compare will generate build scripts that will change every time you drop and re-create system-named constraints or if you re-create a table from a script. Your version control system could easily clog up with insignificant changes if you are using SQL Compare with the default options, and you dare to rebuild tables when you make changes to them.

There is another awkwardness. Before you import data into a table you often want to disable all constraints and then reenable them. You can disable all constraints on a table by doing this:

You can then turn them on with:

Typically, though, scripts do this by identifying each constraint individually, by name, to disable checking and then enabling checks after the data import. Sometimes, they drop and re-create them. The simplest way to access a constraint individually with surgical precision in SQL is to reference it by name. if you do this, your handy data import routine that specifies the name of a constraint that has a system-generated name, created on one version of a database, won’t work on an exact copy of the database on the same server, created with a different script.

Getting SQL Compare to ignore system named constraint and index names

There is a simple solution to all this, and one that aligns Compare’s behavior with that of SSMS (and SMO) default behavior, when generating scripts.

SQL Compare’s option ‘Ignore system named constraint and index names‘ is OFF by default. You should enable it and set it as the default, for your copy of SQL Compare. What this does is to force SQL Compare to recognize that the system-named constraints effectively don’t have a name, so it must use other ways of comparing them.

If you use the command-line version of SQL Compare, you can also specify this as an option. If you use SQL Change Automation, you can specify this in a call to the New-DatabaseReleaseArtifact, Sync-DatabaseSchema, or Invoke-DatabaseBuild cmdlets using the -SQLCompareOptions parameter as IgnoreSystemNamedConstraintAndIndexNames or, to its friends, iscn.

If you generate build scripts for objects or entire databases from SSMS or from SMO using the sqlserver module, then it will represent system-named objects correctly in the script. If you turn on SQL Compare’s ‘Ignore system named constraint and index names‘ option, it will do the same. You might be tempted to check ‘Ignore constraint and index names’ instead, but this won’t fix this problem.

Accessing individual constraints

It has been said that, by using system-named constraints, you make the errors caused by individual constraints difficult to understand. In fact, as I’ve shown in this article, the system-generated names are designed specifically to make it simple to work out what table, constraint type and even column was involved.

It is also said that it makes tables difficult to compare. It certainly requires a different approach. When comparing tables, you need to identify and compare system-named constraints by what they do rather than by their name. SQL Compare is happy to do this if you tell it to.

DEFAULT constraints are easy because a column can only have one. If a default value changes for 20 to 40 then it’s a different constraint. A CHECK constraint on a column is defined by what it does so, effectively, the column and code it executes is the identifier. A table-level CHECK constraint is defined by its code. A FOREIGN KEY constraint is identified by the referencing table, referenced table and a list of columns.

In fact, though, it is rare to justifiably need to DELETE or ALTER a constraint dynamically. Generally, the only time you ever need to access a constraint is to turn it on or off. Then you almost always want to turn them all off or on at once.

Conclusions

The idea of unnamed constraints in SQL is alarming to some developers and DBAs, but if it makes constraints easier for people to use, I’m all for it. In fact, I wish there were more ways of encouraging the use of appropriate constraints.

I always include the option, ‘Ignore system named constraint and index names‘, in SQL Compare and in SCA cmdlets, it is in my default settings, and so it comes as a shock when I use it without this setting because odd things start happening. By including it, you have lot more confidence that object-level and database-level scripts that you generate with SQL Compare will, generally-speaking, work the same way as those generated by SSMS, but with the bonus of error-checking and rollback on error, and the preservation of data.

 

Tools in this post

SQL Change Automation

Automate your database changes with CI and automated deployment

Find out more

SQL Compare

Compare and synchronize SQL Server database schemas

Find out more