1

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. – ypercubeᵀᴹ Aug 8 '16 at 14:32
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)
  • I'm using 10.0.20-MariaDB – Fafanellu Aug 8 '16 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 Aug 8 '16 at 15:50
  • Can you copy my code exactly and try if that works? – Lennart Aug 8 '16 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 Aug 9 '16 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'); – Lennart Aug 9 '16 at 9:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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