FOREIGN KEY references same table's column. Can't insert values

Question

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.

Answer 1

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

SQL Fiddle DEMO

Demo shows inserting and deleting of a parent node

The magic drop part for all children is done by ON DELETE CASCADE