如何使用自动递增的主键作为外键？ [英] How to use an auto incremented primary key as a foreign key as well?

问题描述

这是我想要做的：

我有两个表...

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;

然后一个约束：

ALTER TABLE `parent` ADD FOREIGN KEY (`id`) REFERENCES `child` (`parent_id`);

正如你可以看到，表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.

现在，我想在父表中插入一条记录，如下所示：

Now I want to insert a record in the parent table, like this:

INSERT INTO parent SET DATA="abc";

并且失败，并显示错误：

And it fails with error:

无法添加或更新子行：a
外键约束失败
（ anacorbero 。父，CONSTRAINT
parent_ibfk_1 FOREIGN KEY（ id ）
参考子（ parent_id ））

Cannot add or update a child row: a foreign key constraint fails (anacorbero.parent, CONSTRAINT parent_ibfk_1 FOREIGN KEY (id) REFERENCES child (parent_id))

我理解它失败，因为它没有在子表中找到引用记录。如果我开始在子表中创建记录，将其parent_id设置为1，然后重置父表的自动增加计数器（以便下一个插入将具有id = 1），它的工作原理！但是这不是解决方案。

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.

如果子表中没有相关行，我看不到插入阻塞的实用程序...

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...

（我知道我可以使用JOIN，但我试图使用表关系，用于数据完整性，也作为PHP的元数据）

(I know I can use JOIN, but I'm trying to use table relations, for data integrity and also as metadata for PHP)

推荐答案

反向引用和引用表。您可能想做：

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`);

您也可以在 CREATE TABLE 语句，如下所示：

You can also define the foreign key in the CREATE TABLE statement, as follows:

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;

测试用例：

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 

这篇关于如何使用自动递增的主键作为外键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！