In some hierarchies I have seen people put the the current id in the
parent_ID Field {basicaly pointing to them self} to represent the top of
the hierarchy.
I don't know how much this would affect the rest of your application but
it would get rid of the null's
-----Original Message-----
From: Marcus Bointon [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 14, 2005 11:37 AM
To: mysql@lists.mysql.com
Subject: Foreign key constraint problem
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
