Beware of the System Generated Constraint Name

When I was a programmer many
years ago, I remember telling the Data Administrator, “I don’t care what you
name a table/column, just give me a name you can live with.” Now I’m a DBA and
I still am not really concerned over the naming of SQL Server objects, as long
as the name is acceptable to the Data Administrator. Recently I
changed my attitude and care a lot more about how “Constraint Names” are
defined.

Constraint names are the
kinds of things programmers and Data Administrators really don’t care about.
If you asked one of them they would probable say, “No one writes code that
references constraint names so it is not important to name a constraint; let
the system generate the constraint names.” That statement is close to being
true, especially for the point and click DBA’s. But not all DBA work is done
with wizards and Enterprise manager. Regardless of how you manage change in
your shop, eventually you are bound to end up coding a small piece of T-SQL
code, which will reference a constraint name. Therefore, you had better be
aware that constraint names might change, depending on how you define them.

As a demonstration, imagine you
are creating table B with a foreign key constraint that references table A.
Imagine further that table A was created by using the following T-SQL code:


CREATE TABLE A (
A_COL1 INT NOT NULL PRIMARY KEY,
A_COL2 CHAR(10) )

Then you created table B by
running the following:


CREATE TABLE B (
B_COL1 INT,
B_COL2 INT FOREIGN KEY REFERENCES A (A_COL1),
B_COL3 CHAR(50))

Seems fairly straight
forward, right? Can you see where these two bits of T-SQL code caused SQL
Server to create two system-generated constraint names? SQL Server created
system-generated constraint names for a primary key on table A and a foreign
key definition on table B. To see what constraint names where generated run the
“sp_help” stored procedure (SP) against table A and table B. When I executed
“sp_help” on my server, it showed that the primary key constraint name on table
A was “PK__A__735B0927” and “FK__B__B_COL1__75435199” was the foreign key
constraint name on table B.

The system-generated
constraint names where defined because the above “CREATE TABLE” statements did
not explicitly name the constraints. Be aware that SQL Server generates
constraint names for not only PRIMARY and FOREIGN keys, but also for DEFAULT, UNIQUE
and CHECK constraints. Each time a constraint with a system-generated name is
dropped and recreated, it gets a new name.

Normally having these
system-generated constraint names is not an issue, unless you need to build a
script that references them. In our shop, we manage all database changes by
building T-SQL scripts. Therefore, when we build a change management script
our desired goal is to have a script that runs on any server in our
environment. When we build our scripts for our development database, our goal
is have the same development scripts also work against our QA and production
servers. When we have system-generated names, our scripts will not work on
each server, because each SQL Server creates a slightly different system
generated name each time it creates a constraint name.

For example, let’s say that
after implementing table A and B on all of the servers, (development, QA, and
production), it is determined that A_COL1 and B_COL2 column should be CHAR (4)
instead of an INT. To implement the column definition changes, build the
following script for the development server:


ALTER TABLE B DROP CONSTRAINT FK__B__B_COL1__75435199
ALTER TABLE A DROP CONSTRAINT PK__A__735B0927
ALTER TABLE A ALTER COLUMN A_COL1 CHAR(4) NOT NULL
ALTER TABLE A ADD PRIMARY KEY (A_COL1)
ALTER TABLE B ALTER COLUMN B_COL2 CHAR(4)
ALTER TABLE B ADD FOREIGN KEY (B_COL2) REFERENCES A (A_COL1)

This code runs fine in
development, but when it’s run in QA and/or production, it generates the
following error:


Server: Msg 3728, Level 16, State 1, Line 1
FK__B__B_COL1__75435199 is not a constraint.
Server: Msg 3727, Level 16, State 1, Line 1
Could not drop constraint. See previous errors.

This error is produced
because the constraint name for the foreign key reference is a system-generated
name, and neither the QA nor the production database have the same foreign key
constraint name as the development server database. Therefore, with
system-generated constraint names we are not able to build scripts that will
run on each of our servers without change.

To solve this problem it is
necessary to change the way we define table A and B. Instead of using the code
above to create these tables, change it slightly to specify the constraint
names. The create table scripts now look like this:


CREATE TABLE A (
A_COL1 INT NOT NULL CONSTRAINT PK_A PRIMARY KEY,
A_COL2 CHAR(10) )

CREATE TABLE B (
B_COL1 INT,
B_COL2 INT CONSTRAINT FK_B_B FOREIGN KEY REFERENCES A (A_COL1),
B_COL3 CHAR(50))

Now the script to change
from INT to CHAR(4) will work, without change, in the development, QA and
PRODUCTION servers, and will be coded like this:


ALTER TABLE B DROP CONSTRAINT FK_B_B
ALTER TABLE A DROP CONSTRAINT PK_A
ALTER TABLE A ALTER COLUMN A_COL1 CHAR(4) NOT NULL
ALTER TABLE A ADD CONSTRAINT PK_A PRIMARY KEY (A_COL1)
ALTER TABLE B ALTER COLUMN B_COL2 CHAR(4)
ALTER TABLE B ADD CONSTRAINT FK_B_B FOREIGN KEY (B_COL2) REFERENCES A (A_COL1)

Conclusion

If you are currently
performing maintenance using scripts, then you are already painfully aware of
the impacts of system-generated constraint names. For the rest of you, I hope
this article sheds some light on problems that can occur with system-generated
constraint names. You wouldn’t ask a programmer to build an application where
tables and/or column names kept changing, so don’t allow database designers to
build SQL Server tables without specifying constraint names.

»


See All Articles by Columnist
Gregory A. Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles