MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:SGreen Date:June 14 2005 4:52pm
Subject:Re: Foreign key constraint problem
View as plain text  
Marcus Bointon <marcus@stripped> wrote on 06/14/2005 12:37:18 

> I have a table that uses a self join to represent simple hierarchies.
> I have a parent_id field that contains a reference to the same
> table's id field. Not all items have a parent, so parent_id is nullable.

> The problem I run into is in defining the foreign key constraint - if
> a parent item is deleted, I want all the children to cascade delete.
> But it seems I can't combine cascade deletion with nullable - I can
> never have more than one record with a parent_id of null because the
> insert causes the foreign key constraint to fail. This means I can
> never have more than one tree stored in the table. How should I set
> up this relation so it works how I want? I'd really prefer not to
> maintain it manually...

> Marcus
> --
> Marcus Bointon
> Synchromedia Limited: Putting you in the picture
> marcus@stripped |

My suggestion, lame as it is, would be to use a 0 (zero) in place of the 
NULL value. That way, you always have a valid entry for PARENT_ID, you can 
still identify the tops of the trees (parent_id=0) and you have gotten 
around the only-one-parent-entry-can-be-null trap.

Since 0 (or any other non-positive number) won't be auto-generated by an 
auto_increment field, you should be safe. In fact, you could even pick 
descending negative numbers (-1, -2, -3) as the parent_id of your multiple 
trees as a means of unique identification.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Foreign key constraint problemMarcus Bointon14 Jun
  • Re: Foreign key constraint problemSGreen14 Jun
    • Re: Foreign key constraint problemMarcus Bointon14 Jun
RE: Foreign key constraint problemGordon Bruce14 Jun