mysql - alter table 添加外键失败

Question

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

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

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

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

我明白了:

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)

为什么？？？

附注我正在使用 phpmyadmin 版本。 3.3.9.2

Answer 1

表 videoCat 有一行或多行违反了外键约束。这通常是因为您有一行的 _url 的值在视频表中不存在。

您可以使用以下查询进行检查:

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

编辑

根据请求，这里有一个删除那些讨厌的行的查询:

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