MySQL Bugs: #39651: Referential integrity constraint does not check the presence of target table

Bug #39651	Referential integrity constraint does not check the presence of target table
Submitted:	25 Sep 2008 14:53	Modified:	25 Sep 2008 16:20
Reporter:	Nihar Ranjan Paital	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.0.66a, 5.1.28, 6.0.6	OS:	Any (nihar.paital@lakshyasolutions.com)
Assigned to:		CPU Architecture:	Any

Description:
I run the following query and found problem in
Referential integrity constraint does not check the presence of target table

SQL> show tables;
+-----------------------------------------------------------------+
| Tables_in_test                                                  |
+-----------------------------------------------------------------+
| cc                                                              |
| csql_log_int                                                    |
| shop                                                            |
+-----------------------------------------------------------------+
SQLRowCount returns 3

SQL> CREATE TABLE chicken (cID INT PRIMARY KEY,eID INT REFERENCES egg(eID));
SQLRowCount returns 0
SQL> show tables;
+-----------------------------------------------------------------+
| Tables_in_test                                                  |
+-----------------------------------------------------------------+
| cc                                                              |
| chicken                                                         |
| csql_log_int                                                    |
| shop                                                            |
+-----------------------------------------------------------------+
SQLRowCount returns 4
4 rows fetched

How to repeat:
I run the following query and found problem in
Referential integrity constraint does not check the presence of target table

SQL> show tables;
+-----------------------------------------------------------------+
| Tables_in_test                                                  |
+-----------------------------------------------------------------+
| cc                                                              |
| csql_log_int                                                    |
| shop                                                            |
+-----------------------------------------------------------------+
SQLRowCount returns 3

SQL> CREATE TABLE chicken (cID INT PRIMARY KEY,eID INT REFERENCES egg(eID));
SQLRowCount returns 0
SQL> show tables;
+-----------------------------------------------------------------+
| Tables_in_test                                                  |
+-----------------------------------------------------------------+
| cc                                                              |
| chicken                                                         |
| csql_log_int                                                    |
| shop                                                            |
+-----------------------------------------------------------------+
SQLRowCount returns 4
4 rows fetched

Suggested fix:
When we create a table refering another table the target table should be present before the new table
for ex:
In the above query The "egg" table should be present before executing the query "CREATE TABLE chicken (cID INT PRIMARY KEY,eID INT REFERENCES egg(eID));"

Verified on 5.0.66a:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.66a-enterprise-gpl-nt MySQL Enterprise Server (GPL)

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

mysql> show tables like 'egg%';
Empty set (0.00 sec)

mysql> CREATE TABLE chicken (cID INT PRIMARY KEY,eID INT REFERENCES egg(eID))
    -> engine=MyISAM;
Query OK, 0 rows affected (0.06 sec)

The above is OK (REFERNCES is ignored for engines that does not support foreign keys). But with InnoDB we have the same behaviour:

mysql> drop table chicken;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE chicken (cID INT PRIMARY KEY,eID INT REFERENCES egg(eID))
    -> engine=InnoDB;
Query OK, 0 rows affected (0.16 sec)

mysql> show variables like '%mode%';
+---------------+---------------------------------------------------------------
-+
| Variable_name | Value
 |
+---------------+---------------------------------------------------------------
-+
| sql_mode      | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 |
+---------------+---------------------------------------------------------------
-+
1 row in set (0.00 sec)

I am not sure how this corresponds to http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html...

5.1 and 6.0 are also affected:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.28-rc-community MySQL Community Server (GPL)

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

mysql> show tables like 'egg%';
Empty set (0.03 sec)

mysql> CREATE TABLE chicken (cID INT PRIMARY KEY,eID INT REFERENCES egg(eID))
    -> engine=InnoDB;
Query OK, 0 rows affected (0.16 sec)

mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3311 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.6-alpha-community MySQL Community Server (GPL)

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

mysql> show tables like 'egg%';
Empty set (0.02 sec)

mysql> CREATE TABLE chicken (cID INT PRIMARY KEY,eID INT REFERENCES egg(eID))
    -> engine=InnoDB;
Query OK, 0 rows affected (0.14 sec)

The issue is simply that "REFERENCES" is ignored regardless of whether or not the references are valid.

One creates a foreign key via:

CREATE TABLE chicken ( a INT PRIMARY KEY, FOREIGN KEY (a) REFERENCES egg(a) ) ENGINE=InnoDB;

This statement produces an error.

The InnoDB section of the manual is correct:

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

The CREATE TABLE Syntax section of the manual is not:

http://dev.mysql.com/doc/refman/5.1/en/create-table.html

So, either we have a documentation issue or a parser issue.  This has been reported previously, but I don't know the original report offhand.

Or, if InnoDB in fact should be able to intercept the "REFERENCES" style of constraint syntax, then we're back where we started. :)

Dean is right. MySQL just silently ignores REFERENCES in this case anyway, even in ANSI SQL mode. I'd say it is a bug in MySQL parser and/or manual page for CREATE TABLE.

Will be fixed in 6.1 with WL#148
Dmitri, please push a test case for this as part of Mil.7

The "workaround" to this problem is simple: use the SQL syntax FOREIGN KEY xx REFERENCES ... rather than the "in-line" syntax