ON DELETE CASCADE doesn't work even storage table is InnoDb

Question

I tear my hair with the ON DELETE CASCADE Clause. In fact I have two tables I'm on linux and I have installed lamp with phpmyadmin.

That's the tables :

CREATE TABLE a(
    Idatable bigint(20) PRIMARY KEY NOT NULL AUTO_INCREMENT, 
    Nameatable varchar(50) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE anObject(
    IdAnObject bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Nameobject varchar(20) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE linkTable(
    Idatable bigint(20) NOT NULL REFERENCES a (Idatable),
    IdAnObject bigint(20) NOT NULL REFERENCES anObject (IdAnObject) ON DELETE CASCADE,
    PRIMARY KEY (Idatable, IdAnObject),
    CONSTRAINT linkTable_fk1
        FOREIGN KEY (Idatable) 
        REFERENCES a (Idatable),
    CONSTRAINT linkTable_fk2
        FOREIGN KEY (IdAnObject) 
        REFERENCES anObject (IdAnObject) ON DELETE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

Unfortunately, it doesn't work because when I delete a row in the table 'linktable' the associated row in anObject isn't delete

Thank you

It's working perfectly as @ypercube points out. See here. – Vérace Apr 27 '16 at 15:10 — Vérace, Apr 27 '16 at 15:10

score 3 · Accepted Answer · answered Apr 27 '16 at 13:37

3

The CASCADE clause works in the opposite direction. From the parent to the child table.

When you delete a row in the parent table, the related rows in the child table are deleted as well.

answered Apr 27 '16 at 13:37

ypercubeᵀᴹ

97,895
13
214
305

Just a thought - not a good idea to permanently delete records. Copy them to a logging table via a trigger or set a Boolean instead. – Vérace Apr 27 '16 at 16:36
1

@Vérace I don't delete records because my tables have only rows ;) – ypercubeᵀᴹ Apr 27 '16 at 16:49
@ypercubeᵀᴹ "Columns are not fields. Rows are not records. Tables are not files" :-) – Vérace Apr 27 '16 at 17:23

ON DELETE CASCADE doesn't work even storage table is InnoDb

1 Answers1