Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Here are my tables;

CREATE TABLE `borclular` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci

 CREATE TABLE `dosyalar` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci

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`))
share|improve this question
    
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 at 6:01

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:

https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

share|improve this answer

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.

share|improve this answer
    
I would second that, I never actually use cascading in my designs, it promotes lazy coding and has too much room for error. – Mr.Brownstone Feb 4 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 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 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 at 7:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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