FOREIGN KEY references same table's column. Ca

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

Answer 2

Typically, you will need to allow the 'root' record to have a null parent - i.e. menus.parent_id should be nullable, and the 'root' menu item will have a null parent_id.

i.e.

Change your DDL to:

 parent_id int(10) NULL

And then you add your root element with NULL as the parent_id

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

Then you are good to go with child elements:

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

etc.

SQL Fiddle here