Foreign Keys doesn't work as I expect

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`))

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.

StackExchange Database Administrators Q#128287, answer score: 8