Re: Making a foreign key chain - good idea or bad idea?

On Wed, 24 Jan 2001, Frank Joerdens wrote:

> I just did something which seems to work alright and which makes sense
> to me now but which I have a funny feeling about. It may be good
> standard practice (and I just don't know about it) or dangerously
> foolish or just plain silly: I created a foreign key reference on a
> column that is also the primary key for this table as in
>
> create table institute (
> id int4 references index ( id ) PRIMARY KEY,
> . . .
>
> and then used that column as a reference for a foreign key constraint in
> a couple of other tables:
>
> create table boss (
> institute_id int4 references institute ( id ),
> . . .
>
> create table staff (
> institute_id int4 references institute ( id ),
> . . .
>
> I am not really sure what happens when I delete or modify the id column
> in the table at the top of this "chain". Except for this uncertainty I
> don't think this scheme would pose a problem, but I may be wrong. Can
> anyone enlighten me?

If you mean index(id), as long as there exists an institute(id) that
references it, you won't be allowed to delete or update it to a distinct
value. And, you won't be able to delete or update institute(id) as long
as there exists at least one boss(id) or staff(id) that references it.
You know that there shouldn't be orphaned boss(id) or staff(id) rows
because those can't exist without a institute(id) row of the correct
value and that requires the index(id) value.