MySQL ON DELETE CASCADE not work

Question

i have two table (type innoDb) why when i deleting main table row like

+note(im using arch os so my database server type is maria db)

DELETE FROM buildings 
WHERE
    building_no = 2;

relation table rows dont delete ???

CREATE TABLE buildings (
    building_no INT PRIMARY KEY AUTO_INCREMENT,
    building_name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL
);

CREATE TABLE rooms (
    room_no INT PRIMARY KEY AUTO_INCREMENT,
    room_name VARCHAR(255) NOT NULL,
    building_no INT NOT NULL,
    FOREIGN KEY (building_no)
        REFERENCES buildings (building_no)
        ON DELETE CASCADE
);

For me too. Can not be reproduced. See it working here: http://sqlfiddle.com/#!9/82c235/1/0 — fancyPants, Aug 29 '17 at 07:40
guys i updated note and tag i should say database server type is maria db is cause of my problem ? — moh, Aug 29 '17 at 07:42
Probably not. Likeliest cause is, that you haven't double checked everything like engine type (since you didn't specify it in your create statement). — fancyPants, Aug 29 '17 at 07:46
i rebuild my table with ENGINE=InnoDB; line to end of my sql command but still not work still have the problem — moh, Aug 29 '17 at 07:52
see here i did some building delete http://www.axgig.com/images/79751284206007638635.png building rows is 0 but rooms rows not changed at all — moh, Aug 29 '17 at 07:57
Please check `select @@foreign_key_checks;`. If it is 0, you [disabled the checks](https://stackoverflow.com/q/15501673/6248528) and the cascades cannot work. — Solarflare, Aug 29 '17 at 08:10
@Solarflare it is 0 ! where should i enable foreign key check ? — moh, Aug 29 '17 at 08:14
See the link I added in my comment to set it back. It is enabled by default. If it keeps getting disables, you might have to find out where, but is often disables by e.g. data import scripts (that you may have run lately, are sometimes after your last server restart), so it is probably a one time thing - but keep it in mind. — Solarflare, Aug 29 '17 at 08:19
@Solarflare Why don't you add it as an answer. Would upvote it, since I haven't thought about that. — fancyPants, Aug 29 '17 at 13:22
Have same problem here. both tables are innodb and foreign_key_checks is 1 — Abadis, Feb 03 '22 at 13:11
Mine has foreign_key_check enabled yet on delete cascade is not working! — Drishti Jain, Jun 21 '23 at 10:07

score -1 · Answer 1 · answered Aug 29 '17 at 07:41

This syntax works for me on MySQL database but i can't see issues in your query too

CREATE TABLE  `rooms` (
  room_no INT PRIMARY KEY AUTO_INCREMENT,
  room_name VARCHAR(255) NOT NULL,
  building_no INT NOT NULL,
  CONSTRAINT `FK_rooms_1` FOREIGN KEY (`building_no`) REFERENCES `buildings` 
  (`building_no`) ON DELETE CASCADE
) ENGINE=InnoDB;

or add the constraint after table creation

ALTER TABLE `rooms`
  ADD CONSTRAINT `FK_rooms_1` FOREIGN KEY (`building_no`) REFERENCES `buildings` (`building_no`) ON DELETE CASCADE ON UPDATE CASCADE;

MySQL ON DELETE CASCADE not work

1 Answers1