这是我想要做的:
我有两个表...
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更多推荐
如何使用自动递增的主键作为外键?
发布评论