Bug #47667 Improve ERROR 1005/150 message when trying to create a TEMPORARY table with a FK

• Submitted: 28 Sep 2009 4:18
• Modified: 28 Sep 2009 6:04
• Reporter: Roel Van de Paar
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S4 (Feature request)
• Version: 5.1.37
• OS: Any
• CPU Architecture: Any

[28 Sep 2009 4:18] Roel Van de Paar

Description:
mysql> CREATE TEMPORARY TABLE child (id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE) ENGINE=INNODB;
ERROR 1005 (HY000): Can't create table 'roelt.child' (errno: 150)

Without reason.

How to repeat:
DROP TABLE IF EXISTS child;
DROP TABLE IF EXISTS parent;
CREATE TABLE parent (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE) ENGINE=INNODB;
CREATE TEMPORARY TABLE parent (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TEMPORARY TABLE child (id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE) ENGINE=INNODB;

It should be possible to create the second parent table:

'A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY  table of the same name. (The existing table is hidden until the temporary table is dropped.)'
http://dev.mysql.com/doc/refman/5.1/en/create-table.html

Suggested fix:
Fix behavior, or, list it as a problem here: 
http://dev.mysql.com/doc/refman/5.1/en/temporary-table-problems.html

[28 Sep 2009 4:27] Roel Van de Paar

Not a bug. FOREIGN KEY Constraints are not allowed for TEMPORARY tables.

'Both tables must be InnoDB tables and they must not be TEMPORARY tables.'
http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

[28 Sep 2009 4:34] Roel Van de Paar

A non-existing table reference:

CREATE TABLE child (id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES nonexisting(id) ON DELETE CASCADE) ENGINE=INNODB;

Gives the same error:
ERROR 1005 (HY000): Can't create table 'roelt.child' (errno: 150)

(perror 150: Foreign key constraint is incorrectly formed)

It may be good to make the error in the original situation somewhat clearer. Something like:

mysql> CREATE TEMPORARY TABLE child (id INT, parent_id INT, INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE) ENGINE=INNODB;

ERROR 1005 (HY000): Can't create table 'roelt.child'. FOREIGN KEY Constraints are not allowed on TEMPORARY TABLES (errno: nnn)

With a new perror number.

[28 Sep 2009 6:04] Valeriy Kravchuk

Thank you for the feature request.