当我想要删除表时，外键约束失败

Question

当我尝试的时候

drop tables users;

它显示错误:无法删除或更新父行:外键约束失败

下面是我的表格：

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;

我想我做错了外键，但我不确定如何正确地更改它。谢谢你的建议。

Answer 1

在即将提交删除操作时提交drop table users MySQL检查参照完整性约束是否违反。此检查失败，因为您已经定义了events的以下约束

CONSTRAINTevents_ibfk_5FOREIGN KEY (users_id) REFERENCESusers(id) ON DELETE CASCADE ON UPDATE CASCADE，同时在events表上，users_id字段不能为NULL。因此，在删除表时，users_id被设置为NULL，这会在提交期间失败。解决此问题的一种可能方法是删除events表上users_id字段上的NOT NULL约束。