From: Daevid Vincent
Date: April 4 2003 1:57am
Subject: RE: self foreign keys and InnoDB is it possible ?
List-Archive: http://lists.mysql.com/mysql/136424
Message-Id: <002601c2fa4d$84fe7690$a50aa8c0@Locutus>
MIME-Version: 1.0
Content-Type: text/plain;	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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.=20

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]=20
> Sent: Thursday, April 03, 2003 5:10 PM
> To: mysql@stripped
> Subject: self foreign keys and InnoDB is it possible ?
>=20
>=20
> hi all
>=20
> I'm creating a table like this
>=20
> CREATE TABLE test (
>         id      integer not null primary key auto_increment,
>         test    char(20),
>         father  integer key default =3D'-1' not null,
>         FOREIGN KEY (id) RERERENCES test(padre) ON DELETE CASCADE
> ) Type=3DInnoDB;
>=20
> But I made some mistakes on it, because i want it to act differently
> from now
>=20
> I want obtain something like this
>=20
> 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
>=20
> 3-6
>=20
> and then I want to delete all child from a id that is their father.
>=20
> example=20
> 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
>=20
> I hope this could be enough to explain my SQL problem...
>=20
> TIA
> ALx
> --=20
> alx <alexs81@stripped>
>=20
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   =20
> http://lists.mysql.com/mysql?> unsub=3Ddaevid@stripped
>=20