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

[25 Sep 2008 14:53] Nihar Ranjan Paital
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));"
[25 Sep 2008 16:22] Valeriy Kravchuk
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...
[25 Sep 2008 16:23] Valeriy Kravchuk
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)
[26 Sep 2008 19:22] Dean Ellis
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.
[26 Sep 2008 19:23] Dean Ellis
Or, if InnoDB in fact should be able to intercept the "REFERENCES" style of constraint syntax, then we're back where we started. :)
[26 Sep 2008 19:45] Valeriy Kravchuk
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.
[26 Sep 2008 20:16] Konstantin Osipov
Will be fixed in 6.1 with WL#148
Dmitri, please push a test case for this as part of Mil.7
[23 Oct 2008 23:13] Omer Barnir
The "workaround" to this problem is simple: use the SQL syntax FOREIGN KEY xx REFERENCES ... rather than the "in-line" syntax

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.062 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.