Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Beware to the System Generated Constraint Name

By Gregory Larsen,

Beware to the System Generated Constraint Name

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.

 

 

 

 

 

Total article views: 7056 | Views in the last 30 days: 2
 
Related Articles
SCRIPT

Generate DDL script to drop table constraints

Execute SP to generate alter statements to drop PK, FK, Check Constraints and Default Constraints on...

ARTICLE

Stairway to SQL Server Indexes: Level 12, Create Alter Drop

Options and impacts when creating, altering and dropping an index

SCRIPT

Generate DDL script to create table and all constraints

Generate all DDL statements for a given table (Create Table, PK, FK, Check Constraints, Defaults)

FORUM

How to Generate Alter Table Script Dynamically.

Want to genereate Alter Table Script .....

FORUM

Script Alter Tables

I want to Generate Script Alter Tables Created

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones