MySQL on delete cascade remove parent_id on pk_id remove

Question

I don't want to have to write a php recursive function to handle it.

I have a lookup table which generates a list of articles and categories, each has it's own unique id (nav_id) and if it's a child it has a parent_id (articles are never used as a parent however).

So my thoughts were CASCADE DELETE, which keeps running into violation parent_id cannot be null. However this is how I identify root.

SQL

CREATE TABLE IF NOT EXISTS `navigation` (
`nav_id` INT NOT NULL AUTO_INCREMENT COMMENT '',
`parent_id` INT NULL COMMENT '',
`article_id` INT NULL DEFAULT 0 COMMENT '',
`category_id` INT NULL DEFAULT 0 COMMENT '',
`position` TINYINT NULL COMMENT '',
PRIMARY KEY (`nav_id`, `parent_id`)  COMMENT '',
UNIQUE INDEX `nav_id_UNIQUE` (`nav_id` ASC)  COMMENT '',
INDEX `rec1_idx` (`parent_id` ASC)  COMMENT '',
CONSTRAINT `rec1`
FOREIGN KEY (`parent_id`)
REFERENCES `navigation` (`nav_id`)
ON DELETE CASCADE
ON UPDATE NO ACTION)
ENGINE = InnoDB

However INSERT result for:

':parent_id' => null
':article_id' => null
':category_id' => 19
':position' => 1


INSERT INTO navigation (
parent_id,article_id,category_id,position
) VALUES (
:parent_id,:article_id,:category_id,:position
)

is

Integrity constraint violation: 1048 Column 'parent_id' cannot be null

Any and all ideas are welcome.

Table structural change? Foreign key change?

I also tried a trigger on after and before delete however you can't delete from the same table the trigger is executed on.

See if this helps: http://stackoverflow.com/questions/441988/mysql-foreign-key-to-allow-null — Maximus2012, Apr 01 '16 at 21:33
Another one: http://stackoverflow.com/questions/15082874/how-to-pass-a-null-value-to-a-foreign-key-field — Maximus2012, Apr 01 '16 at 21:35
Nope. Either multiple tables or they had issue with primary key null. I have even altered my CREATE TABLE SQL DEFAULT parent_id = null and set it in PHPMyAdmin tick box null, same result. Works fine if I remove the constraint but I need the on delete cascade — Darcey, Apr 01 '16 at 21:42
What happens when you try to remove the foreign key constraint. Additionally, may you can move the `nav_id-parent_id` relationship to a separate table ? — Maximus2012, Apr 01 '16 at 21:43
ideally I would like to keep it all in 1 table. it will be used to output a html ul li nested list of any depth. — Darcey, Apr 01 '16 at 21:52

score 0 · Answer 1 · answered Apr 01 '16 at 21:43

0

MySQL is not allowing you to insert null value in parent_id because it's a part of primary key. See below:

PRIMARY KEY (`nav_id`, `parent_id`)  COMMENT ''

You can't insert null values for Primary Key columns. If you remove parent_id from primary key, the insert will work fine. Here is SQL Fiddle.

answered Apr 01 '16 at 21:43

Darshan Mehta

30,102
11
68
102

Cannot delete or update a parent row: a foreign key constraint fails (`navigation`, CONSTRAINT `rec1` FOREIGN KEY (`parent_id`) REFERENCES `navigation` (`nav_id`))' in – Darcey Apr 01 '16 at 21:47
PS. Nice I didn't know SQLFiddle existed :) I'm googling the foreign key error now. – Darcey Apr 01 '16 at 21:48
Are you trying to delete records? – Darshan Mehta Apr 01 '16 at 21:49
DELETE FROM navigation WHERE nav_id = 4; Error is as above. The main reason for me adding the foreignkey was to delete cascase any nav_id's that become parent_id's without wiring a php recursive function. – Darcey Apr 01 '16 at 21:51
Looks fine to me too... I'm reading some people have issues with corruption or something and recommend drop db and re-create (oh joy). But on xampp localhost I get: Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`navigation`, CONSTRAINT `rec1` FOREIGN KEY (`parent_id`) REFERENCES `navigation` (`nav_id`))' in (****.php) – Darcey Apr 01 '16 at 22:00
works on parent_id = null however anything else it doesn't, for me. Replicating on SQLFiddle now see if that works. array (size=1) ':navigation_id' => string '2' (length=1) ############### string 'DELETE FROM navigation WHERE nav_id = :navigation_id' (length=52) ############### nav_id,parent_id,article_id,category_id,position 2,NULL,NULL,19,1 3,2,NULL,20,1 – Darcey Apr 01 '16 at 22:04
SQL Fiddle link I posted above works for both null and not null parent ids. – Darshan Mehta Apr 01 '16 at 22:06
Thanks for your info and help, much appreciated. :) – Darcey Apr 01 '16 at 22:34

Darcey · Answer 2 · 2016-04-02T09:09:32.507

COMPLETE ANSWER

PART 1

FROM Darshan Mehta on SQL Table creation to fix null insert allowance

MySQL is not allowing you to insert null value in parent_id because it's a part of primary key. See below:

PRIMARY KEY (`nav_id`, `parent_id`)  COMMENT ''

PART 2

The main issue, "CASCADE DELETE" the following SQL works perfectly:

CREATE TABLE IF NOT EXISTS `navigation` (
`nav_id` INT NOT NULL AUTO_INCREMENT COMMENT '',
`parent_id` INT NULL COMMENT '',
`article_id` INT NULL DEFAULT 0 COMMENT '',
`category_id` INT NULL DEFAULT 0 COMMENT '',
`position` TINYINT NULL COMMENT '',
PRIMARY KEY (`nav_id`)  COMMENT '',
UNIQUE INDEX `nav_id_UNIQUE` (`nav_id` ASC)  COMMENT '',
INDEX `rec1_idx` (`parent_id` ASC)  COMMENT '',
CONSTRAINT `rec1`
FOREIGN KEY (`parent_id`)
REFERENCES `navigation` (`nav_id`)
ON DELETE CASCADE
ON UPDATE NO ACTION
);

http://sqlfiddle.com/#!9/5026ee/1/0

MySQL on delete cascade remove parent_id on pk_id remove

2 Answers2