Bug #18768 Cannot create foreign keys

• Submitted: 4 Apr 2006 8:27
• Modified: 4 Apr 2006 8:48
• Reporter: Tobias Asplund
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.1.7
• OS: Any (*)
• CPU Architecture: Any

[4 Apr 2006 8:27] Tobias Asplund

Description:
This is probably not a bug, probably because of the global FK in 5.1, but makes applications behave bad for testers, so if it's not intentional, figured I'd submit it.
(Did a search, but couldn't find it anywhere either).

eeyore> DROP TABLE IF EXISTS bugtest;
Query OK, 0 rows affected (0.01 sec)

eeyore> DROP TABLE IF EXISTS bugtest2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

eeyore> CREATE TABLE bugtest ( a INT PRIMARY KEY );
Query OK, 0 rows affected (0.01 sec)

eeyore> CREATE TABLE bugtest2 ( a INT, INDEX (a));
Query OK, 0 rows affected (0.01 sec)

eeyore> ALTER TABLE bugtest2 ADD FOREIGN KEY (a) REFERENCES bugtest (a) */ Note, no warnings, no nothing */;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

eeyore> SHOW CREATE TABLE bugtest2 \G
*************************** 1. row ***************************
       Table: bugtest2
Create Table: CREATE TABLE `bugtest2` (
  `a` int(11) default NULL,
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

eeyore> INSERT INTO bugtest2 VALUES (1) /* This should fail if the FK was there */;
Query OK, 1 row affected (0.01 sec)

How to repeat:
DROP TABLE IF EXISTS bugtest;

DROP TABLE IF EXISTS bugtest2;

CREATE TABLE bugtest ( a INT PRIMARY KEY );

CREATE TABLE bugtest2 ( a INT, INDEX (a));

ALTER TABLE bugtest2 ADD FOREIGN KEY (a) REFERENCES bugtest (a);

SHOW CREATE TABLE bugtest2 \G

INSERT INTO bugtest2 VALUES (1) /* This should fail if the FK was there */;

[4 Apr 2006 8:48] Heikki Tuuri

This is a duplicate of http://bugs.mysql.com/bug.php?id=18477