Marcus Bointon <[EMAIL PROTECTED]> wrote on 06/14/2005 12:37:18 PM: > 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 > [EMAIL PROTECTED] | http://www.synchromedia.co.uk 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
