Delete record from DB with possible childrens

Question

In DB i have only one table: navigation (InnoDB) enter image description here

Is it possible configure this table to: If i delete where id == 1, all id 1 (Root Item) subs will be deleted automatically?

UPDATE

I try to recreate table:

CREATE TABLE `navigation` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` VARCHAR(32),
`url` VARCHAR(32),
`position` int(10),
`parent_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `type` (`parent_id`)
CONSTRAINT `??????` FOREIGN KEY (`parent_id`)
  REFERENCES `types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

But What value need in line 9?

Here table already exist. – Kārlis Millers Oct 15 '13 at 12:22 — Kārlis Millers, Oct 15 '13 at 12:22

score 0 · Accepted Answer · answered Oct 15 '13 at 15:22

0

The ?????? should be the foreign key you are creating (your_key_name) between the tables. 'parent_id' in navigation is linked to 'id' in types. When the navigation table has an update or delete done, the delete/update is cascaded to the types table.

CONSTRAINT `navigate2types` FOREIGN KEY (`parent_id`)
  REFERENCES `types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

Remember that you need to use the InnoDB storage engine.

answered Oct 15 '13 at 15:22

Steven Scott

10,234
9
69
117

Now when run query: /* SQL Error (1005): Can't create table 'cms.navigation' (errno: 150) Foreign key constraint is incorrectly formed */ – Kārlis Millers Oct 16 '13 at 09:04

Delete record from DB with possible childrens

1 Answers1