Description:
http://databasejournal.com/features/mssql/article.php/1570801
...
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.
How to repeat:
CREATE TABLE foo (id int not null primary key) TYPE=InnoDB;
CREATE TABLE bar
(
id int not null primary key,
ref int,
index (ref),
constraint `bar_ref_foo` foreign key (ref) references foo (id)
) TYPE=InnoDB;
show create table bar\G
*************************** 1. row ***************************
Table: bar
Create Table: CREATE TABLE `bar` (
`id` int(11) NOT NULL default '0',
`ref` int(11) default NULL,
PRIMARY KEY (`id`),
KEY `ref` (`ref`),
CONSTRAINT `0_1661` FOREIGN KEY (`ref`) REFERENCES `foo` (`id`)
) TYPE=InnoDB
1 row in set (0.00 sec)
Suggested fix:
honor constraint names for foreign keys, etc.
