List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:April 4 2003 1:57am
Subject:RE: self foreign keys and InnoDB is it possible ?
View as plain text  
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
> 

Thread
self foreign keys and InnoDB is it possible ?alx4 Apr
  • RE: self foreign keys and InnoDB is it possible ?Daevid Vincent4 Apr