FOREIGN KEY refers to the same table. Unable to insert values

Question

I created a table with FOREIGN KEY and cannot 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 a FOREIGN KEY to automatically remove children when a parent has been deleted. This table was created successfully, but I cannot 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 do not want to look for children in php code, so I need to somehow create a simple table with three columns and automatically drop all children and children.

Answer 1

For all your needs you must 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 ); 

SQL Fiddle DEMO

Demo shows insert and delete parent node

The magic part for all children is performed using ON DELETE CASCADE

Answer 2

Typically, you need to allow root entries to have a null parent element, i.e. menus.parent_id must be NULL, and the root menu item will be null parent_id .

i.e.

Change your DDL to:

  parent_id int(10) NULL 

And then you add your root element with NULL as parent_id

 insert into `menus` (id, `label`, parent_id) VALUES (1, 'label1', null); 

Then you should go with the children:

 insert into `menus` (id, `label`, parent_id) VALUES (2, 'subitem1', 1); 

and etc.

SQL feed here