Bug #2194 Foreign key restraints not working in 4.1.0

• Submitted: 22 Dec 2003 16:10
• Modified: 22 Dec 2003 22:40
• Reporter: Sean Canaday
• Status: Can't repeat
• Category: MySQL Server: Command-line Clients
• Severity: S1 (Critical)
• Version: 4.1.0
• OS: Windows (Windows 2000)
• CPU Architecture: Any

[22 Dec 2003 16:10] Sean Canaday

Description:
Innodb foreign key restraints do not work properly.  My SQL might not be correct but I've included exactly what I typed and results.

How to repeat:
mysql> create database ref;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id),
    -> FOREIGN KEY (parent_id) REFERENCES parent(id)
    -> ON DELETE CASCADE
    -> ) TYPE=INNODB;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into parent values (5);
Query OK, 1 row affected (0.00 sec)

mysql> insert into child values (1,5);
Query OK, 1 row affected (0.02 sec)

mysql> delete from parent where id = 5;
Query OK, 1 row affected (0.00 sec)

mysql> select * from parent;
Empty set (0.00 sec)

mysql> select * from child;
+------+-----------+
| id   | parent_id |
+------+-----------+
|    1 |         5 |
+------+-----------+
1 row in set (0.00 sec)

[22 Dec 2003 22:40] Alexander Keremidarski

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

Your statements are correct, but I can't reproduce your problem.

Check if InnoDB is enabled and if tables you create are of InnoDB type. 

SHOW CREATE TABLE parent;
SHOW CREATE TABLE child;

If InnoDB is disabled MySQL will silently accept your statements and will create MyISAM which don't support Foreign Keys

By the way upgrade to 4.1.1