I am a bit confused as to why a table would have a foreign key to
its own primary key as was shown in the Alzabo manpage (copied
location_id tinyint -- primary key
location varchar(200) -- 'New York City'
parent_location_id tinyint -- foreign key to
could someone please explain that to me?
From: Dave Rolsky <autarch@ur...> - 2001-12-17 18:30:34
On Mon, 17 Dec 2001, Terrence Brannon wrote:
> I am a bit confused as to why a table would have a foreign key to
> its own primary key as was shown in the Alzabo manpage (copied
> TABLE: Location
> location_id tinyint -- primary key
> location varchar(200) -- 'New York City'
> or 'USA'
> parent_location_id tinyint -- foreign key to
> could someone please explain that to me?
Well, in this particular case the idea is to represent parent-child
relationships, a tree structure.
For example, the 'top' location might be 'World', under which we might
have the continents, under which we would have countries, each of which
has states or provinces, each of which has cities, which can have
You get the idea.
So we need a way to link them all together and that's what the foreign key
is for. There is a 1..N relationship here. A child can have only a
single parent, and a parent can have many children.
Alzabo::MethodMaker can detect these relationships and auto-generate some
useful methods. Actually, I should probably add more methods like
'descendants' and 'ancestors' to follow the tree all the way.
Of course, in the presence of cycles this is going to be a real problem.
Alzabo could auto-detect them, I guess.
Anyway, all this _should_ be supported natively by the DBMS but none of
them really do so its nice to have Alzabo do it.
We await the New Sun