Beware to the System Generated Constraint Name
By Gregory A. Larsen
When I was a programmer many years ago I remember telling
the Data Administrator, “I don’t care what you name a table/column, but just
give me a name you can live with.” Well
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. Although
recently I changed my attitude and now a 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 probable would 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, sooner or later you
are bound to end up coding a small piece of T-SQL code, which will reference a
constraint name. Therefore you better
be aware, that constraint names might change, depending on how you define your
constraints.
Here let me demonstrate what I mean. Say you are creating table B that has a
foreign key constraint that references table A, and 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 too created 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 shows 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 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 gets dropped
and recreated it gets a new name.
Normally having these system-generated constraint names are
not an issue, unless you need to build a script that needs to reference them.
In our shop we manage all database changes by building T-SQL scripts. So 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 out 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 the constraint name is
created.
To show you what I mean, lets say that after implementing
table A and B, on all of our servers (development, QA, and production), we
determine that A_COL1 and column B_COL2 should be CHAR (4) instead of an
INT. To implement the column definition
changes we build the following script for our 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 run fine in development, but when we run this code
in QA and/or production we get 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.
This problem can be solved it we change the way we define
table A and B. Instead of using the
code above to create these tables, we change it slightly to specify the
constraint names. Doing this our create
table scripts would 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))
If we did this then our script to change from INT to CHAR(4)
would work, without change, in our development, QA and PRODUCTION servers and
would 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)
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 shed 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
name.
