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]
