Beware of the System Generated Constraint Name
January 16, 2003
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)
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.