MySQL Bugs: #44053: FK constraint name not stored when created with ALTER TABLE command

Bug #44053	FK constraint name not stored when created with ALTER TABLE command
Submitted:	2 Apr 2009 20:06	Modified:	10 Apr 2013 15:45
Reporter:	Lewis Stockett	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.67, 4.1, 5.0, 5.1, 6.0 bzr	OS:	Any (MS Windows XP [Version 5.1.2600], Linux)
Assigned to:	John Russell	CPU Architecture:	Any
Tags:	ALTER TABLE, Constraint Name, foreign key

Description:
The problem is similar to Bug 6340. When you create a table and later add a foreign key constraint, the constraint name is ignored and a system-generated constraint name is used instead.

How to repeat:
CREATE TABLE foo
(
	my_first_key BIGINT NOT NULL,
	my_second_key BIGINT NOT NULL,
	my_text_field VARCHAR(128) NOT NULL,
	 PRIMARY KEY (my_first_key,my_second_key)
);

CREATE UNIQUE INDEX XPKHOUSEHO ON FSC_HOUSEHOLD_ACCOUNT_BRIDGE
(
	my_first_key,
	my_second_key
);

CREATE INDEX XIF1HOUSEHO ON FSC_HOUSEHOLD_ACCOUNT_BRIDGE
(
	my_first_key
);

ALTER TABLE foo
	ADD FOREIGN KEY my_cons_name (my_first_key) REFERENCES BAR(my_first_key);

show create table foo;

The foreign key name will not be "my_cons_name" as expected. It will be system generated.

Suggested fix:
This bug and bug 6340 are almost identical. It looks like the fix for 6340 just needs to be applied to the situation where constraints are created with an ALTER TABLE instead of a CREATE TABLE.

Instructions for replicating bug contain a cut and paste error. Here a corrected script:

CREATE TABLE foo
(
	my_first_key BIGINT NOT NULL,
	my_second_key BIGINT NOT NULL,
	my_text_field VARCHAR(128) NOT NULL,
	 PRIMARY KEY (my_first_key,my_second_key)
);

CREATE UNIQUE INDEX XPKFOO ON foo
(
	my_first_key,
	my_second_key
);

CREATE INDEX XIF1FOO ON foo
(
	my_first_key
);

ALTER TABLE foo
	ADD FOREIGN KEY my_cons_name (my_first_key) REFERENCES BAR(my_first_key);
	
show create table foo;

The name of the constraint won't be "my_cons_name" as expected. It will be the index name.

Thank you for the report.

Verified as described.

Workaround:

ALTER TABLE foo ADD CONSTRAINT my_cons_name FOREIGN KEY  (my_first_key) REFERENCES bar(my_first_key);

Hello!

The reported behavior is not a bug but an expected behavior.

When you do:

ALTER TABLE child ADD CONSTRAINT a FOREIGN KEY b (fk) REFERENCES parent (pk)

Two things happen:
 - We create a foreign key on child's column fk, referencing parent columns pk. Then name of the foreign key is 'a'.
 - We create a supporting index (if no appropriate index created explicitly) for this foreign key which is called 'b'.

When "CONSTRAINT a" clause omitted like in your case server still uses 'b' as a name of supporting index, but generates name of foreign key automatically.

I agree that our documentation at http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html doesn't explains the situation clearly.

So I am marking this bug a "Documentation" bug.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Since the problem affects ALTER TABLE, I'll add this to the ALTER TABLE page:

For ALTER TABLE, unlike CREATE TABLE, ADD FOREIGN KEY ignores
index_name if given and uses an automatically generated foreign key
name. As a workaround, include the CONSTRAINT clause to specify the
foreign key name:

ADD CONSTRAINT name FOREIGN KEY (....) ...