MySQL 如何将自动递增的主键也用作外键？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3544322/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use an auto incremented primary key as a foreign key as well?
提问by Rolf
This is what I'm trying to do:
这就是我想要做的：
I have 2 tables...
我有两张桌子...
CREATE TABLE `parent` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`data` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
CREATE TABLE `child` (
`parent_id` int(11) DEFAULT NULL,
`related_ids` int(11) DEFAULT NULL,
KEY `parent_id` (`parent_id`),
KEY `related_ids` (`related_ids`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
And then a constraint:
然后是一个约束：
ALTER TABLE `parent` ADD FOREIGN KEY (`id`) REFERENCES `child` (`parent_id`);
As you can see the table parent has an auto-incremented primary key "id", which is also being used as a foreign key for the child table.
正如你所看到的，父表有一个自动递增的主键“id”，它也被用作子表的外键。
Now I want to insert a record in the parent table, like this:
现在我想在父表中插入一条记录，如下所示：
INSERT INTO parent SET DATA="abc";
And it fails with error:
它失败并出现错误：
Cannot add or update a child row: a foreign key constraint fails (
myschema.parent, CONSTRAINTparent_ibfk_1FOREIGN KEY (id) REFERENCESchild(parent_id))
无法添加或更新子行：外键约束失败 (
myschema.parent, CONSTRAINTparent_ibfk_1FOREIGN KEY (id) REFERENCESchild(parent_id))
I understand that it fails because it doesn't find a referred record in the child table. If I start by creating a record in the child table, set it's parent_id to 1, then reset the auto-increment counter of the parent table (so that the next insert will have id = 1), it works! But that's not a solution.
我知道它失败是因为它没有在子表中找到引用记录。如果我首先在子表中创建一条记录，将其 parent_id 设置为 1，然后重置父表的自动递增计数器（以便下一次插入的 id = 1），它就可以工作了！但这不是解决方案。
I don't see the utility of the insert blocking if there is no related row in the child table...
如果子表中没有相关行，我看不到插入阻塞的效用...
I'm just trying to do a one-to-many relationship...
我只是想建立一对多的关系......
(I know I can use JOIN, but I'm trying to use table relations, for data integrity and also as metadata for PHP)
（我知道我可以使用 JOIN，但我正在尝试使用表关系，以确保数据完整性以及作为 PHP 的元数据）
回答by Daniel Vassallo
It looks like you have the referencing and referenced tables in reverse. You may want to do:
看起来您的引用表和被引用表是相反的。你可能想做：
ALTER TABLE `child ` ADD FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`);
You can also define the foreign key in the CREATE TABLEstatement, as follows:
也可以在CREATE TABLE语句中定义外键，如下：
CREATE TABLE `parent` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`data` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
CREATE TABLE `child` (
`parent_id` int(11) DEFAULT NULL,
`related_ids` int(11) DEFAULT NULL,
KEY `parent_id` (`parent_id`),
KEY `related_ids` (`related_ids`),
FOREIGN KEY (`parent_id`) REFERENCES `parent`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Test case:
测试用例：
INSERT INTO parent (`data`) VALUES ('test data 1');
Query OK, 1 row affected (0.01 sec)
INSERT INTO parent (`data`) VALUES ('test data 2');
Query OK, 1 row affected (0.01 sec)
INSERT INTO child (`parent_id`, `related_ids`) VALUES (1, 100);
Query OK, 1 row affected (0.01 sec)
INSERT INTO child (`parent_id`, `related_ids`) VALUES (2, 100);
Query OK, 1 row affected (0.01 sec)
INSERT INTO child (`parent_id`, `related_ids`) VALUES (3, 100);
ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails
回答by Rolf
Uh... I think I got it backwards. It seems that I need to add the foreign key to the child table, like that:
呃...我想我倒退了。看来我需要将外键添加到子表中，如下所示：
ALTER TABLE `child` ADD FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`);
I'm having a hard time dealing with MySQL terminology. Can you blame me?
我很难处理 MySQL 术语。你能怪我吗？
