MySQL - cascading restriction does not do this job properly when deleted

Question

I have some tables:

• users full of users.
• item full of elements.
• itemmay have multiple second_items.
• usersmay have second_itemthrough the table users_items.

When I delete a item, all the elements second_itemand users_itemsmust also be removed. Therefore, I set some restrictions that should remove records from child tables. But he leaves a note in users_items.

Here are my MySQL queries:

CREATE TABLE `item` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(63) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `item` (`id`, `name`) VALUES (1, 'test');

CREATE TABLE `second_item` (
  `id` int(10) UNSIGNED NOT NULL,
  `item_id` int(10) UNSIGNED DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `second_item` (`id`, `item_id`, `name`) VALUES
(1, 1, 'test');

CREATE TABLE `users` (
  `id` int(10) UNSIGNED NOT NULL,
  `email` varchar(200) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `users` (`id`, `email`, `password`) VALUES (1, 'test', 'test');

CREATE TABLE `users_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED DEFAULT NULL,
  `item_id` int(10) UNSIGNED DEFAULT NULL,
  `item_second_id` int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `users_items` (`id`, `user_id`, `item_id`, `item_second_id`) VALUES (1, 1, 1, 1);

ALTER TABLE `second_item`
  ADD PRIMARY KEY (`id`),
  ADD KEY `item_id` (`item_id`);

ALTER TABLE `item` ADD PRIMARY KEY (`id`);

ALTER TABLE `users` ADD PRIMARY KEY (`id`);

ALTER TABLE `users_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `item_id` (`item_id`),
  ADD KEY `item_second_id` (`item_second_id`);


ALTER TABLE `second_item`
  ADD CONSTRAINT `constraint1` FOREIGN KEY (`item_id`) REFERENCES `item` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `users_items`
  ADD CONSTRAINT `constraint2` FOREIGN KEY (`item_id`) REFERENCES `item` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `constraint3` FOREIGN KEY (`item_second_id`) REFERENCES `second_item` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `constraint4` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

I am trying to execute the following delete request, but it leaves an entry in users_items:

DELETE FROM `item` WHERE `item`.`id` = 1;

This error appears:

• WAMP (server version: 5.7.16 - MySQL community server (GPL))
• UBUNTU (5.7.20-0ubuntu0.16.04.1 - (Ubuntu))

Edition:

The error is caused by the fact that you are adding restrictions:

ALTER TABLE `users_items`
  ADD CONSTRAINT `constraint1` FOREIGN KEY (`item_id`) REFERENCES `item` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `constraint2` FOREIGN KEY (`item_second_id`) REFERENCES `second_item` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `constraint3` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `second_item`
  ADD CONSTRAINT `constraint4` FOREIGN KEY (`item_id`) REFERENCES `item` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

, .

Answer 1

, . ;)

SQL. .

, MySQL Bug