更改表添加外键失败 [英] alter table add foreign key fails

问题描述

我有3个表，它们都有innodb引擎:

I have 3 tables and they all have innodb engine:

video(url, title, desc, country,...) url -> primary key
videoCat(_url, category) {_url,category} -> primary key
favorite(fav_url, thumb_path) fav_url -> primary key

然后我这样做:

alter table favorite
add foreign key(fav_url) references video(url)
on delete cascade

一切顺利，但是当我尝试时:

and everything goes smooth, but when I try:

alter table videoCat
add foreign key(_url) references video(url)
on delete cascade

我得到:

1452-无法添加或更新子行:外键约束失败(bascelik_lookaroundyou.<结果2在解释文件名'#sql-efa_1a6e91a'> ;, CONSTRAINT #sql-efa_1a6e91a_ibfk_1外键(_url)时在删除级联上引用video(url)

1452 - Cannot add or update a child row: a foreign key constraint fails (bascelik_lookaroundyou.<result 2 when explaining filename '#sql-efa_1a6e91a'>, CONSTRAINT #sql-efa_1a6e91a_ibfk_1 FOREIGN KEY (_url) REFERENCES video (url) ON DELETE CASCADE)

为什么?

p.s.我正在使用phpmyadmin ver. 3.3.9.2

p.s. I am using phpmyadmin ver. 3.3.9.2

推荐答案

表videoCat包含一行或多行违反外键约束的行.通常，这是您在表视频中有一行的_url值不存在.

The table videoCat has one or more rows that violates the foreign key constraint. This is usually that you have a row with a value for _url that does not exist in the table video.

您可以通过以下查询进行检查:

You can check for this with the following query:

SELECT videoCat._url
FROM videoCat LEFT JOIN video ON videoCat._url = video.url
WHERE video.url IS NULL

编辑

每个请求，以下是删除这些讨厌行的查询:

Per request, here's a query to delete those pesky rows:

DELETE FROM videoCat
WHERE NOT EXISTS (
    SELECT *
    FROM video
    WHERE url = videoCat._url
)

这篇关于更改表添加外键失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！