FOREIGN KEY引用同一个表的列。不能插入值 [英] FOREIGN KEY references same table's column. Can't insert values
问题描述
CREATE TABLE菜单(
$ b) $ b $ id int(10),
parent_id int(10),
标签varchar(255),
PRIMARY KEY(id),
FOREIGN KEY(parent_id)REFERENCES菜单id)
);
我需要FOREIGN KEY来在父项被删除时自动删除子项。
INSERT INTO`menus`(`parent_id`,`label`)这个表已成功创建,但是我无法插入任何东西。 )
VALUES('1','label1');
或
INSERT INTO`menus`(`label`)
VALUES('label1');
#1452 - 无法添加或更新子行:外键约束失败
I真的不想在PHP代码中寻找任何孩子,所以我需要以某种方式创建一个简单的3列表,并自动删除所有的孩子,他们的孩子也一样。
$ p $ CREATE TABLE`menus`(
)
(11)unsigned NOT NULL AUTO_INCREMENT,
`parent_id` int(11)unsigned DEFAULT NULL,$ b $``varchar(255)NOT NULL DEFAULT'',
PRIMARY KEY (`id`),
key`fk_parent_menu`(`parent_id`),
CONSTRAINT`fk_parent_menu` FOREIGN KEY(`parent_id`)
REFERENCES`menus`(`id`)ON DELETE CASCADE ON UPDATE CASCADE
);
演示显示插入和删除父节点
所有孩子的魔术部分都是由 ON DELETE CASCADE
I created table with FOREIGN KEY and can't insert anything.
CREATE TABLE menus (
id int(10),
parent_id int(10),
label varchar(255),
PRIMARY KEY (id),
FOREIGN KEY (parent_id) REFERENCES menus (id)
);
I need FOREIGN KEY to automatically delete children when parent was deleted. This table was successfully created but I can't insert anything.
INSERT INTO `menus` (`parent_id`, `label`)
VALUES ('1', 'label1');
or
INSERT INTO `menus` (`label`)
VALUES ( 'label1');
#1452 - Cannot add or update a child row: a foreign key constraint fails
I really don't want look for any children in php code so I need somehow create simple table with 3 columns and automatically drop all children and they children too.
For all your needs you should take this structure
CREATE TABLE `menus` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`parent_id` int(11) unsigned DEFAULT NULL,
`label` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `fk_parent_menu` (`parent_id`),
CONSTRAINT `fk_parent_menu` FOREIGN KEY (`parent_id`)
REFERENCES `menus` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);
Demo shows inserting and deleting of a parent node
The magic drop part for all children is done by ON DELETE CASCADE
这篇关于FOREIGN KEY引用同一个表的列。不能插入值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!