Foreign key constraint fails when I want delete table

Question

When I am trying

drop tables users;

It shows error: Cannot delete or update a parent row: a foreign key constraint fails

Here are my tables:

CREATE TABLE `events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(128) NOT NULL,
  `users_id` int(11) NOT NULL,
  `capacity` int(64) NOT NULL,
  `event_date` datetime NOT NULL,
  `text` text CHARACTER SET utf8 COLLATE utf8_czech_ci,
  `last_edit` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `users_id` (`users_id`),
  CONSTRAINT `events_ibfk_5` FOREIGN KEY (`users_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(100) NOT NULL,
  `password` varchar(100) NOT NULL,
  `role` enum('admin','member','guest','registered') NOT NULL DEFAULT 'registered',
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `users_data` (
  `id` int(120) NOT NULL AUTO_INCREMENT,
  `users_id` int(11) DEFAULT NULL,
  `name` varchar(50) NOT NULL,
  `username` varchar(50) NOT NULL,
  `phone_number` varchar(25) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `users_id` (`users_id`),
  CONSTRAINT `users_data_ibfk_5` FOREIGN KEY (`users_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `user_matches` (
  `user_id` int(120) NOT NULL,
  `match_id` int(11) NOT NULL,
  KEY `user_id` (`user_id`),
  KEY `match_id` (`match_id`),
  CONSTRAINT `user_matches_ibfk_4` FOREIGN KEY (`user_id`) REFERENCES `users_data` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `user_matches_ibfk_5` FOREIGN KEY (`match_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I think I did wrong foreign key but I am not sure how to change it correctly. Thank you for your any advice.

Answer 1

When you submit drop table users MySQL checks for referential integrity constraints violation when the deletion is about to be committed. This check fails because you have defined the following constraint of events:

CONSTRAINTevents_ibfk_5FOREIGN KEY (users_id) REFERENCESusers(id) ON DELETE CASCADE ON UPDATE CASCADE and at the same time on the events table the users_id field cannot be NULL. Therefore, upon dropping the table, the users_id is set to NULL which fails during commit. One possible way to work around this problem is to remove the NOT NULL constraint on the users_id field on the events table.