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 straightforward, 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.