6

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.

Sir Rufo
  • 18,395
  • 2
  • 39
  • 73
user1927652
  • 91
  • 1
  • 5
  • 2
    You say you want to "automatically delete children", yet all you show us are the inserts. What exactly is your question? –  Jan 03 '13 at 12:31
  • 3
    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`. – StuartLC Jan 03 '13 at 12:31
  • 1
    what values do you want to have for id? I didn't see any of them inserted or autogenerated – Sir Rufo Jan 03 '13 at 12:32
  • I think you missed the autoincrement on id column – Luca Rainone Jan 03 '13 at 12:45

2 Answers2

10

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

Sir Rufo
  • 18,395
  • 2
  • 39
  • 73
  • @SirRufo It doesn't work for `update`. check http://sqlfiddle.com/#!9/445052/1/0 and then try adding `Update menus set id = 6 WHERE id = 1;` you will get `#1451 - Cannot delete or update a parent row` – Sam Nov 12 '17 at 05:25
  • @Sam It doesn't work for updating the identity field. Why do you ever want to update/change the value of an autogenerated identity field? – Sir Rufo Nov 12 '17 at 07:25
  • @SirRufo Completely agree with you that in practical situations PK is never updated. Just wanted to add this comment to mention that this is a known [documented](https://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html) limitation (or [bug](https://bugs.mysql.com/bug.php?id=24668) ) in mysql. – Sam Nov 13 '17 at 11:06
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

StuartLC
  • 104,537
  • 17
  • 209
  • 285