mysql 删除受影响行_mysql – 删除行时出现问题

当我做的事情：

DELETE FROM users_has_pais WHERE users_id = 151

我收到以下错误消息：

SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (gisele.users_has_pais, CONSTRAINT users_has_pais_ibfk_3 FOREIGN KEY (users_id) REFERENCES users (id) ON DELETE NO ACTION ON UPDATE NO ACTION)

我不确定问题是什么,虽然我猜它与ON DELETE NO ACTION有关.我不知道我应该改变什么,但是…作为参考我粘贴了我的表结构的转储：

CREATE TABLE IF NOT EXISTS `users` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`email` varchar(50) DEFAULT NULL,

`password` varchar(250) DEFAULT NULL,

`url` varchar(50) DEFAULT NULL,

`responsable` varchar(50) DEFAULT NULL,

`role` varchar(25) DEFAULT NULL,

`fecha` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`id`),

UNIQUE KEY `email` (`email`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=162 ;

CREATE TABLE IF NOT EXISTS `pais` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`pais` varchar(20) NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `pais` (`pais`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

users_has_pais | CREATE TABLE `users_has_pais` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`users_id` int(11) NOT NULL,

`pais_id` int(11) NOT NULL,

PRIMARY KEY (`id`),

KEY `user` (`users_id`),

KEY `index3` (`pais_id`),

KEY `users_id` (`users_id`),

KEY `pais_id` (`pais_id`),

CONSTRAINT `users_has_pais_ibfk_3` FOREIGN KEY (`users_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,

CONSTRAINT `users_has_pais_ibfk_2` FOREIGN KEY (`pais_id`) REFERENCES `pais` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=latin1 |

解决方法:

您正在尝试删除由另一个表中的约束引用的记录.因此,您需要执行以下操作之一：

>如果在您的逻辑中有意义,则将约束更改为级联删除(或其他约束规则)

>警告用户问题,并建议他先删除其他对象

>使您的应用程序删除相关行,然后删除您尝试删除的行(根据@RacerX注释).

更新

在查看了包含更多详细信息的数据结构后,我怀疑您的约束可能会向后定义.你能发布约束定义吗？

标签：mysql,sql,constraints,database-design

来源： https://codeday.me/bug/20190630/1338468.html