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:alexs81@stripped]
> Sent: Thursday, April 03, 2003 5:10 PM
> To: mysql@stripped
> 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 <alexs81@stripped>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?> unsub=daevid@stripped
>
