MySQL on delete cascade doesn't work...I'm using mac os x lion and sequel pro

Question

I cannot make it work. An example from http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

CREATE TABLE parent (id INT NOT NULL,
                     PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (id INT, parent_id INT,
                    INDEX par_ind (parent_id),
                    FOREIGN KEY (parent_id) REFERENCES parent(id)
                     ON DELETE CASCADE
) ENGINE=INNODB;

What am I doing wrong?

What do you mean? Does your CREATE fail, or does your DELETE on a parent row fail to delete the children rows referencing it? — Unai Vivi, Jan 31 '12 at 13:38
I can create tables, but when I delete a parent, his children doesn't delete — alvizouri, Jan 31 '12 at 20:57

score 1 · Answer 1 · answered Jan 02 '13 at 21:30

When creating the child table your reference has to be a constraint

CONSTRAINT FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE

If that fails, use the following query to check if your configuration checks for references

SHOW VARIABLES LIKE 'foreign_key_checks'

MySQL on delete cascade doesn't work...I'm using mac os x lion and sequel pro

1 Answers1