Bug #44815 cannot add self referential foreign key with ALTER TABLE
Submitted: 12 May 2009 7:54 Modified: 12 May 2009 8:26
Reporter: Stewart Smith Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:6.0.10 OS:Any
Assigned to: CPU Architecture:Any

[12 May 2009 7:54] Stewart Smith
Description:
A self referential foreign key can only be created at CREATE TABLE time, not with ALTER TABLE.

How to repeat:
mysql [localhost] {msandbox} (test) > CREATE TABLE `messages` (
    -> 
    ->   `message_id` int NOT NULL AUTO_INCREMENT,
    -> 
    ->   `message_text` varchar(255),
    -> 
    ->   `next_message_id` int,
    -> 
    ->   PRIMARY KEY (`message_id`)
    -> 
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql [localhost] {msandbox} (test) > alter table messages add constraint fk_next_message foreign key (next_message_id) references messages;
ERROR 1005 (HY000): Can't create table 'test.#sql-4730_3' (errno: 150)

But creating the fkey in the CREATE TABLE works:
drizzle> CREATE TABLE `messages` (    `message_id` int NOT NULL AUTO_INCREMENT,    `message_text` varchar(255),    `next_message_id` int,    PRIMARY KEY (`message_id`), foreign key (next_message_id) references messages(message_id)  ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)

mysql [localhost] {msandbox} (test) > CREATE TABLE `messages` (    `message_id` int NOT NULL AUTO_INCREMENT,    `message_text` varchar(255),    `next_message_id` int,    PRIMARY KEY (`message_id`), foreign key (next_message_id) references messages(message_id)  ) ENGINE=InnoDB;

Suggested fix:
ALTER table is allowed to create the foreign key.

This is just an InnoDB fix as the InnoDB engine returns the failed create table (ha_create) during MySQL performing the ALTER TABLE.
[12 May 2009 8:26] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

You missed column reference from ALTER TABLE. Change "alter table messages add constraint fk_next_message foreign key (next_message_id) references messages;" to "alter table messages add constraint fk_next_message
foreign key (next_message_id) references messages(message_id);"