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

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