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

  • Run SHOW CREATE TABLE ChildTable; and add the output in the question. Commented Aug 8, 2016 at 14:32

1 Answer 1


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

, PRIMARY KEY (id_campagne) ) ENGINE=INNODB;

, 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 

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)
  • I'm using 10.0.20-MariaDB
    – Fafanellu
    Commented Aug 8, 2016 at 15:42
  • I'm doing exactly the same thing, but the child table still contains something even if the related parent has been deleted ! But Campagne PK is FK in another table (which still doesn't have any row so far). Could the problem come from this ?
    – Fafanellu
    Commented Aug 8, 2016 at 15:50
  • Can you copy my code exactly and try if that works? Commented Aug 8, 2016 at 16:26
  • It doesn't. I've done exactly the same thing. Everything is similar (except the id_campagne which starts at 1, but I manually re-assigned it proper values). But after deleting the campagne with id=0, the childs still remain !
    – Fafanellu
    Commented Aug 9, 2016 at 8:43
  • What is the result of: select table_name, engine from information_schema.tables where table_name in ('ChildTable','Campagne');. Also: select * from information_schema.KEY_COLUMN_USAGE where table_name in ('ChildTable','Campagne'); Commented Aug 9, 2016 at 9:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.