foreign key creation fails

[Marcus Eriksson]

 CREATE TABLE `messages` (

  `message_id` int NOT NULL AUTO_INCREMENT,

  `message_text` varchar(255),

  `next_message_id` int,

  PRIMARY KEY (`message_id`)

) ENGINE=InnoDB

drizzle> alter table messages add constraint fk_next_message foreign key (next_message_id) references messages;

ERROR 1005 (HY000): Can't create table 'hibtest.#sql-25b8_2' (errno: 150)

[Padraig O'Sullivan (posulliv)]

Confirmed on trunk.

$ ../client/drizzle --user=root --port=9306
Welcome to the Drizzle client.. Commands end with ; or \g.
Your Drizzle connection id is 1
Server version: 7.0.0 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

drizzle> use test
Database changed
drizzle> 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.00 sec)

drizzle> alter table messages add constraint fk_next_message foreign key (next_message_id)
    -> references messages;
ERROR 1005 (HY000): Can't create table 'test.#sql-37c_1' (errno: 150)
drizzle>

-Padraig

[Stewart Smith (stewart)]

Same result on MySQL 6.0.10

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;

See also http://bugs.mysql.com/bug.php?id=44815

Filed as InnoDB bug in above bug number.

[Stewart Smith (stewart)]

Sveta's comment on mysql bug:
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);"