Here are my tables;

CREATE TABLE `borclular` (
  `tc` char(11) COLLATE utf8_turkish_ci NOT NULL,
  `borclu` varchar(255) COLLATE utf8_turkish_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tc` (`tc`)

 CREATE TABLE `dosyalar` (
  `kurum_id` int(11) NOT NULL,
  `borclu_id` int(11) NOT NULL,
  `hizmetno` varchar(255) COLLATE utf8_turkish_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `kurum_id_index` (`kurum_id`),
  KEY `borclu_id_index` (`borclu_id`),
  CONSTRAINT `borclu_id_foreign` FOREIGN KEY (`borclu_id`) REFERENCES `borclular` (`id`),
  CONSTRAINT `kurum_id_foreign` FOREIGN KEY (`kurum_id`) REFERENCES `kurumlar` (`id`)

When I delete a row from borculular, I want related row from dosyalar to be also deleted.However, when I try to delete a row from borclular, I get the following error;

Cannot delete or update a parent row: a foreign key constraint fails (`iletisim`.`dosyalar`, CONSTRAINT `borclu_id_foreign` FOREIGN KEY (`borclu_id`) REFERENCES `borclular` (`id`))
  • Your expectation is pretty much the exact oppoaite of what you should want to have happen. On delete cascade does one thing, prevents orphan records, but not in a useful way. Orphan records are only a problem because they indicate that there was once something that was deemd important, and part of it was deleted -- either it was deleted in error or it wasn't important. If it wasn't important, then the orphans don't matter and can be deleted at leisure. If it was important cascading a delete hides that it was deleted in error, instead of preventing it. – jmoreno Feb 5 '16 at 6:01

As pointed out by Mr Brownstone (this would just be a comment but it turned out too long) the behaviour you are expecting can be achieved if you define the key as ON DELETE CASCADE.

I would be very very careful with ON DELETE CASCADE, in fact I almost never use it and generally recommend against it. This counts for triggers that take action based upon rows being deleted too. If a naive programmer later implements UPSERT of a parent row by instead performing a DELETE followed by an INSERT (this is a very common pattern in my experience) then the child data is all lost because the delete is cascaded and the rows removed by the cascade are obviously not replaced by the subsequent insert.

Also if your UI allows the user, due to a bug, to try delete something they really shouldn't because rows elsewhere depend upon it, you (or the user) get an error instead of the database silently deleting the wrong thing and its children.

Not having the cascade option turned on sometimes means you need extra work in your data access or business logic layer to manually delete child objects before parents, but I consider this a small price to pay usually.

Caveats: Some consider my position to be one of unnecessary paranoia. Your mileage may vary.

  • I would second that, I never actually use cascading in my designs, it promotes lazy coding and has too much room for error. – World Wide DBA Feb 4 '16 at 15:41
  • I can also advise agains cascading operations from the DB side and a good practice is to let to the business logic the responsabilities to know the correct deletion order. – jean Feb 4 '16 at 17:52
  • 1
    Once saw a user delete ~90% of a database by deleting one row accidentally. Absolutely be careful with this. – Kenneth Fisher Feb 4 '16 at 18:48
  • Paranoia is never unnecessary unless you deliberately want your business to fail (in which case your boss should be paranoid) – slebetman Feb 5 '16 at 7:04

You need to specify that you want the delete action to cascade like so:

CONSTRAINT `borclu_id_foreign` 
FOREIGN KEY (`borclu_id`) REFERENCES `borclular` (`id`) ON DELETE CASCADE

If you do not specify either the ON DELETE or ON UPDATE then it defaults to RESTRICT, from the documentation:

For an ON DELETE or ON UPDATE that is not specified, the default action is always RESTRICT.

Which can be found here:


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.