0

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.

Adogam Miguel
  • 23
  • 1
  • 6
  • Possible duplicate of [Can't drop table: A foreign key constraint fails](https://stackoverflow.com/questions/11100911/cant-drop-table-a-foreign-key-constraint-fails) – chb Feb 07 '18 at 23:24

1 Answers1

0

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.

nick.katsip
  • 868
  • 3
  • 12
  • 32
  • Hi, I just tried to set users_id field to NULL, but still, I cannot drop users table. – Adogam Miguel Feb 08 '18 at 09:00
  • Also i tried to run innodb status and it shows: LATEST FOREIGN KEY ERROR ------------------------ 2018-02-08 10:14:15 70000f1fb000 Cannot drop table `bp`.`users` because it is referenced by `bp`.`events` – Adogam Miguel Feb 08 '18 at 09:20
  • 1
    Can you try then deferring the constraints with the statement `SET foreign_key_checks = 0;`, then try the deletion of the table and then re-enable the foreign key checks with: `SET foreign_key_checks = 1;`? – nick.katsip Feb 08 '18 at 14:18