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: | |
| 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
[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.