I don't have the answer to your question, but I find your question very interesting.
One observation,and I could be wrong, is that "deleting 1 has to delete 1,2,4,5' doesn't seem to be legal as far as Foreign Key constraints go. As I read your table, 1/aa has no father (-1), 2/bb has father 1/aa, but 4/dd and 5/ee have father of 2/bb NOT 1/aa, 1/aa is a grandfather. While I see your logic (if 2/bb doesn't exist, then 4/dd and 5/ee can't exist either), but you are getting into recursion here I think, which I'm pretty sure is beyond the scope of SQL. You might have to break this into a routine in whatever language you're using, one that traverses your tree and deletes the nodes. I'm sure there are some efficient algorithms out there for this type of thing. > -----Original Message----- > From: alx [mailto:[EMAIL PROTECTED] > Sent: Thursday, April 03, 2003 5:10 PM > To: [EMAIL PROTECTED] > Subject: self foreign keys and InnoDB is it possible ? > > > hi all > > I'm creating a table like this > > CREATE TABLE test ( > id integer not null primary key auto_increment, > test char(20), > father integer key default ='-1' not null, > FOREIGN KEY (id) RERERENCES test(padre) ON DELETE CASCADE > ) Type=InnoDB; > > But I made some mistakes on it, because i want it to act differently > from now > > I want obtain something like this > > table row example: > id test father > 1 'aa' -1 > 2 'bb' 1 > 3 'cc' -1 > 4 'dd' 2 > 5 'ee' 2 > 6 'ff' 3 > so relatives chains are > 1-2-4 > \-5 > > 3-6 > > and then I want to delete all child from a id that is their father. > > example > deleting 3 has to delete 3 and 6 > deleting 2 has to delete 2,4,5 > deleting 1 has to delete 1,2,4,5 > > I hope this could be enough to explain my SQL problem... > > TIA > ALx > -- > alx <[EMAIL PROTECTED]> > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
