创建关系:#1452 - 无法添加或更新子行:外键约束失败 [英] Creating relations: #1452 - Cannot add or update a child row: a foreign key constraint fails

问题描述

我试图创建一个新的表关系，但我似乎遇到了一个错误，我不知道为什么.我建立的第一个关系(一对多)是上周，效果很好，也许从那时起我忘记了正确的程序?

I've tried to create a new table relation but I seem to have gotten an error for which I'm not sure why. The first relation (one to many) I made was last week and it works good, maybe I forgot the correct procedure since then?

简单说明:我有三张桌子 - 活动、音乐风格、场地

Simple explanation: I have three tables - events, music_styles, venues

到目前为止，我正确地将活动和场地关联起来，就像一个场地可以链接多个活动一样.我尝试制作一个新表，其中包含每个音乐风格的行和一个将在表事件"中使用的 ID，以便我可以将每个事件链接到一个音乐风格 ID.

So far, I correctly linked events and venues in the way that one venue can have multiple events linked to it. I tried making a new table that has rows for each musical style and an ID that would be used in table 'events' so that I can link each event to a musical style id.

然而这是我得到的错误:

However this is the error I got:

SQL 查询:

ALTER TABLE events 添加外键(MUSIC_STYLE_ID)参考nightl7_complete.music_styles ( MUSIC_ID ) ON DELETE 无操作更新无操作;

ALTER TABLE events ADD FOREIGN KEY ( MUSIC_STYLE_ID ) REFERENCES nightl7_complete.music_styles ( MUSIC_ID ) ON DELETE NO ACTION ON UPDATE NO ACTION ;

MySQL 说:文档

失败 (nightl7_complete/#sql-2721_c0dcfd, CONSTRAINT#sql-2721_c0dcfd_ibfk_2 外键(MUSIC_STYLE_ID)参考music_styles (MUSIC_ID) ON DELETE NO ACTION ON UPDATE NO ACTION)文档错误 ALTER TABLE events ADD FOREIGN KEY (MUSIC_STYLE_ID ) 参考 nightl7_complete.music_styles (MUSIC_ID ) ON DELETE NO ACTION ON UPDATE NO ACTION ;

fails (nightl7_complete/#sql-2721_c0dcfd, CONSTRAINT #sql-2721_c0dcfd_ibfk_2 FOREIGN KEY (MUSIC_STYLE_ID) REFERENCES music_styles (MUSIC_ID) ON DELETE NO ACTION ON UPDATE NO ACTION) Documentation Error ALTER TABLE events ADD FOREIGN KEY ( MUSIC_STYLE_ID ) REFERENCES nightl7_complete.music_styles ( MUSIC_ID ) ON DELETE NO ACTION ON UPDATE NO ACTION ;

这是我做的程序:)

我在这里上传了一张全尺寸的图片

谢谢大家:)

所以我这样做了:

$query = 'SELECT e.* FROM events e '.
    'LEFT JOIN nightl7_complete.music_styles ms ON ms.ID = e.MUSIC_STYLE_ID'.
    'WHERE ms.id IS NULL';
$result = mysql_query($query);
if (!$result) {
  die('Invalid query: ' . mysql_error());
}

得到这个:

无效查询:您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行的ms.id IS NULL"附近使用的正确语法

Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ms.id IS NULL' at line 1

推荐答案

Do you want to link MUSIC_STYLE_ID to ID, not MUSIC_ID>?

Don't you want to link MUSIC_STYLE_ID to ID, not MUSIC_ID?

此外，您需要使用来自 music_styles 的有效 ID 预先填充 MUSIC_STYLE_ID，或者在创建外键之前将它们设置为 null.

Also, you need to either pre-populate MUSIC_STYLE_ID with valid ids from music_styles, or set them to null before creating the foreign key.

要验证这一点，请尝试:

To verify this, try:

SELECT e.*
  FROM events e
  LEFT JOIN nightl7_complete.music_styles ms ON ms.ID = e.MUSIC_STYLE_ID
  WHERE ms.id IS NULL;

如果该查询返回任何记录，则那些是 events 中不包含 music_styles

If that query returns any records, those are entries in events that don't contain corresponding records in music_styles

这篇关于创建关系:#1452 - 无法添加或更新子行:外键约束失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！