最新消息:20190529 VPS服务器已从腾讯云香港换为Vultr新加坡,主题仍用朋友推荐的大前端D8

【已解决】Sequel去删除mysql的表出错:MySQL said: Cannot delete or update a parent row: a foreign key constraint fails

MySQL crifan 327浏览 0评论

Sequel中,去右键删除某个表:

结果报错:

Error

Couldn’t delete ‘media’.

Selecting the ‘Force delete’ option may prevent this issue, but may leave the database in an inconsistent state.

MySQL said: Cannot delete or update a parent row: a foreign key constraint fails

注意到,有另外一个table表:media_keyword_rel

中的有内容,和此处media有关系

所以去删除了这个表的所有的内容:

delete from media_keyword_rel;

然后再去删除之前的表,错误依旧。

Selecting the ‘Force delete’ option may prevent this issue, but may leave the database in an inconsistent state.

MySQL said: Cannot delete or update a parent row: a foreign key constraint fails

postgresql – Force drop db while others may be connected – Database Administrators Stack Exchange

sql – Cannot delete or update a parent row: a foreign key constraint fails – Stack Overflow

Cannot delete or update a parent row: a foreign key constraint fails – MYSQL – Stack Overflow

主外键表关联数据的同时删除-帅小伙的博客-51CTO博客

注意到,另外那个表

media_keyword_rel

的relations部分,是有和media这个表相关的:

而基本明白此处的问题了:

由于此处的别的表(media_keyword_rel)有外键,关联到了此处要彻底删除的表(meida)

导致报错,无法删除。

MySQL出现无法删除行记录 – Agoly – 博客园

MYSQL: Cannot delete or update a parent row: a foreign key constraint fails – CSDN博客

此处没有搞懂那个复杂的:

ALTER TABLE `advertisers`

  ADD CONSTRAINT `advertisers_ibfk_1` FOREIGN KEY (`advertiser_id`)

      REFERENCES `jobs` (`advertiser_id`);

的写法的逻辑。

貌似是可以用:

SET FOREIGN_KEY_CHECKS=0; — to disable them

SET FOREIGN_KEY_CHECKS=1; — to re-enable them

去实现此处的目的的。

-》而中文的帖子,都是这种简单粗暴的办法。

-〉因为帖子提醒了,可能会有风险“This is not a solution to the problem, but rather a dirty work-around that may not be desired.”

-》此处简单的表的关系来说,貌似倒是可以这么简单粗暴的操作的。至少暂时没那么多数据。

而其他几个和meida关联的表的数据都是空的。

media_keyword_rel

CREATE TABLE `media_keyword_rel` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `mediaId` int(11) NOT NULL,

  `keywordId` int(11) NOT NULL,

  `active` char(1) DEFAULT NULL,

  `createTime` datetime DEFAULT NULL,

  `modifyTime` datetime DEFAULT NULL,

  `createUser` int(11) DEFAULT NULL,

  `modifyUser` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `fk_media_keyword_rel_keyword_idx` (`keywordId`),

  KEY `fk_media_keyword_rel_media1_idx` (`mediaId`),

  CONSTRAINT `fk_media_keyword_rel_keyword` FOREIGN KEY (`keywordId`) REFERENCES `keyword` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,

  CONSTRAINT `fk_media_keyword_rel_media1` FOREIGN KEY (`mediaId`) REFERENCES `media` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE=InnoDB AUTO_INCREMENT=9505 DEFAULT CHARSET=utf8;

media_scene_rel

CREATE TABLE `media_scene_rel` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `mediaId` int(11) NOT NULL,

  `sceneId` int(11) NOT NULL,

  PRIMARY KEY (`id`),

  KEY `fk_media_scene_rel_media1_idx` (`mediaId`),

  KEY `fk_media_scene_rel_scene1_idx` (`sceneId`),

  CONSTRAINT `fk_media_scene_rel_media1` FOREIGN KEY (`mediaId`) REFERENCES `media` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,

  CONSTRAINT `fk_media_scene_rel_scene1` FOREIGN KEY (`sceneId`) REFERENCES `scene` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

MySQL – Foreign keys and “Cannot delete or update a parent row: a foreign key constraint fails” error – Blog dbi services

所以此处:

  • 要么去找找Sequel提到的Force delete

    • sequel Selecting the ‘Force delete’ option may prevent this issue, but may leave the database in an inconsistent state.

    • 没搜到。

  • 要么(确保此处不会有其他不良影响的前提下)去:

    • SET FOREIGN_KEY_CHECKS=0;

    • 删除数据

    • SET FOREIGN_KEY_CHECKS=1;

而之前的那段ALTER TABLE的回复,大概有点点看懂了:

指的是其自己的业务逻辑中,应该是

advertisers references jobs

然后重新修改table的关系为:

ALTER TABLE `jobs`

  ADD CONSTRAINT `advertisers_ibfk_1` FOREIGN KEY (`advertiser_id`)

      REFERENCES `advertisers` (`advertiser_id`);

然后delete的操作:

DELETE FROM `jobs` WHERE `job_id` =1 LIMIT 1

应该就可以了。

而我此处不是:

我是要彻底干掉这个meida的表

而别的表(media_keyword_rel和media_scene_rel都)存在外键,和此media有关联。

所以此处简单了,就是用上面的粗暴的方法去操作即可。

1.关闭(外键检查的)限制:

SET FOREIGN_KEY_CHECKS=0;

2.去操作,删除表:

3.(更新了个别字段后,重新)创建之前的表

CREATE TABLE `media` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `domain` char(20) NOT NULL,

  `name` char(200) DEFAULT NULL,

  `resouceId` char(200) NOT NULL,

  `duration` float DEFAULT NULL,

  `createTime` datetime DEFAULT NULL,

  `modifyTime` datetime DEFAULT NULL,

  `createUser` int(11) DEFAULT NULL,

  `modifyUser` int(11) DEFAULT NULL,

  `active` char(1) DEFAULT ‘Y’,

  `publisher` text,

  `authors` char(50) DEFAULT NULL,

  `series` char(200) DEFAULT NULL,

  `seriesNumber` int(11) NOT NULL DEFAULT ‘1’,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4.重新开启(外键检查的)限制:

如此即可满足需求。

且之前的两个表(media_keyword_rel和media_scene_rel)的逻辑结构也还是没有变的,正常的外键依赖。

转载请注明:在路上 » 【已解决】Sequel去删除mysql的表出错:MySQL said: Cannot delete or update a parent row: a foreign key constraint fails

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
68 queries in 0.096 seconds, using 18.81MB memory