ON DELETE CASCADE has no effect

Question

I've been searching SE forums in order to solve that, however I wasn't able to find out where I was wrong.

In my DB I want to link in a strong way a parent table to a child table : to do so, I want all the child table rows that reference the parent to be deleted when the parent is deleted.

My app is not in production, it uses MariaDB, and PHP mysqli-like functions. All the tables use InnoDB engine. The keys involved (parent primary, and foreign key) belong to the same type.

Here is how the install script creates the tables (simplified). The rows are simply concatenated in order to produce a cleaner view of the query. PHP $ are not shown here.

Parent table :

       "CREATE TABLE Campagne( ".
       "id_campagne INT(3) NOT NULL AUTO_INCREMENT, ".
       "nom VARCHAR(100) NOT NULL, ".
                ...
       "PRIMARY KEY (id_campagne),".
       "FOREIGN KEY (utili_num) REFERENCES Utilisateur (utili_num)) ENGINE=INNODB;"

Child table :

       "CREATE TABLE ChildTable ( ".
       "id_child INT(9) NOT NULL AUTO_INCREMENT, ".
                ...
       "id_campagne INT(3) NOT NULL, ".
       "CONSTRAINT `FK_campagne_id_campagne` FOREIGN KEY (`id_campagne`) REFERENCES Campagne (`id_campagne`) ON DELETE CASCADE ,".
       "PRIMARY KEY (id_donnee)) ENGINE=INNODB;"

First of all, I can't use PHP my admin to make a quick delete of a Campagne row (no row is affected when I click on delete). When I use the SQL console, to do something like "DELETE FROM Campagne WHERE id_campagne=3" the corresponding row is properly deleted, however this has no effect on the child rows from the child table that have the matching foreign key.

What am I doing wrong ? Thanks for reading

Answer 1

What version of MySQL are you using? I tested the following with 10.1.16-MariaDB, and it appears to be working:

CREATE TABLE Campagne
( id_campagne INT NOT NULL AUTO_INCREMENT
, PRIMARY KEY (id_campagne) ) ENGINE=INNODB;

CREATE TABLE ChildTable 
( id_child INT(9) NOT NULL AUTO_INCREMENT
, id_campagne INT NOT NULL
, PRIMARY KEY (id_child)
, CONSTRAINT FK_campagne_id_campagne 
      FOREIGN KEY (id_campagne) 
      REFERENCES Campagne (id_campagne) ON DELETE CASCADE 
) ENGINE=INNODB;

insert into Campagne (id_campagne) values (DEFAULT);
insert into ChildTable (id_child, id_campagne) values (DEFAULT, 0);
select * from Campagne;                                         
+-------------+
| id_campagne |
+-------------+
|           0 |
+-------------+

select * from ChildTable;
+----------+-------------+
| id_child | id_campagne |
+----------+-------------+
|        0 |           0 |
+----------+-------------+

delete from Campagne where id_campagne = 0;
select * from ChildTable;
Empty set (0.00 sec)