Description:
The CASCADE delete based on FOREIGN KEYS does not work with mysql-5.1.*
Compiled mysql-5.1.30 ...
It works with mysql-5.0.45
I always compile mysql from sources.
How to repeat:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.1.30 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
DROP DATABASE IF EXISTS Test ;
CREATE DATABASE Test ;
USE Test ;
DROP TABLE IF EXISTS Persons ;
CREATE TABLE IF NOT EXISTS Persons ( Id bigint unsigned not null auto_increment PRIMARY KEY , Name varchar(60) , FamilyName varchar(60) , INDEX Id (Id) ) TYPE=InnoDB ;
DROP TABLE IF EXISTS Accounts ;
CREATE TABLE IF NOT EXISTS Accounts ( Id bigint unsigned not null auto_increment PRIMARY KEY , PersonId bigint unsigned REFERENCES Persons(Id) , Number varchar(60) , INDEX Id (Id) , FOREIGN KEY (PersonId) REFERENCES Persons(Id) ON DELETE CASCADE ) TYPE=InnoDB ;
INSERT INTO Persons (Name,FamilyName) VALUES ('Erno','Hatos') ;
INSERT INTO Accounts (PersonId,Number) VALUES (1,'U000006') ;
mysql> select * from Persons ;
+----+------+------------+
| Id | Name | FamilyName |
+----+------+------------+
| 1 | Erno | Hatos |
+----+------+------------+
1 row in set (0.00 sec)
mysql> select * from Accounts ;
+----+----------+---------+
| Id | PersonId | Number |
+----+----------+---------+
| 1 | 1 | U000006 |
+----+----------+---------+
1 row in set (0.00 sec)
mysql> delete from Persons ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from Accounts ;
+----+----------+---------+
| Id | PersonId | Number |
+----+----------+---------+
| 1 | 1 | U000006 |
+----+----------+---------+
1 row in set (0.00 sec)
Suggested fix:
?
Description: The CASCADE delete based on FOREIGN KEYS does not work with mysql-5.1.* Compiled mysql-5.1.30 ... It works with mysql-5.0.45 I always compile mysql from sources. How to repeat: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 5.1.30 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. DROP DATABASE IF EXISTS Test ; CREATE DATABASE Test ; USE Test ; DROP TABLE IF EXISTS Persons ; CREATE TABLE IF NOT EXISTS Persons ( Id bigint unsigned not null auto_increment PRIMARY KEY , Name varchar(60) , FamilyName varchar(60) , INDEX Id (Id) ) TYPE=InnoDB ; DROP TABLE IF EXISTS Accounts ; CREATE TABLE IF NOT EXISTS Accounts ( Id bigint unsigned not null auto_increment PRIMARY KEY , PersonId bigint unsigned REFERENCES Persons(Id) , Number varchar(60) , INDEX Id (Id) , FOREIGN KEY (PersonId) REFERENCES Persons(Id) ON DELETE CASCADE ) TYPE=InnoDB ; INSERT INTO Persons (Name,FamilyName) VALUES ('Erno','Hatos') ; INSERT INTO Accounts (PersonId,Number) VALUES (1,'U000006') ; mysql> select * from Persons ; +----+------+------------+ | Id | Name | FamilyName | +----+------+------------+ | 1 | Erno | Hatos | +----+------+------------+ 1 row in set (0.00 sec) mysql> select * from Accounts ; +----+----------+---------+ | Id | PersonId | Number | +----+----------+---------+ | 1 | 1 | U000006 | +----+----------+---------+ 1 row in set (0.00 sec) mysql> delete from Persons ; Query OK, 1 row affected (0.00 sec) mysql> select * from Accounts ; +----+----------+---------+ | Id | PersonId | Number | +----+----------+---------+ | 1 | 1 | U000006 | +----+----------+---------+ 1 row in set (0.00 sec) Suggested fix: ?