pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMaking a foreign key chain - good idea or bad idea?
2+ messages / 2 participants
[nested] [flat]
* Making a foreign key chain - good idea or bad idea?
@ 2001-01-24 17:30 Frank Joerdens <frank@joerdens.de>
2001-01-24 20:30 ` Re: Making a foreign key chain - good idea or bad idea? Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 2+ messages in thread
From: Frank Joerdens @ 2001-01-24 17:30 UTC (permalink / raw)
To: pgsql-sql
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?
Ta, Frank
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Making a foreign key chain - good idea or bad idea?
2001-01-24 17:30 Making a foreign key chain - good idea or bad idea? Frank Joerdens <frank@joerdens.de>
@ 2001-01-24 20:30 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 2+ messages in thread
From: Stephan Szabo @ 2001-01-24 20:30 UTC (permalink / raw)
To: Frank Joerdens <frank@joerdens.de>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2001-01-24 20:30 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-24 17:30 Making a foreign key chain - good idea or bad idea? Frank Joerdens <frank@joerdens.de>
2001-01-24 20:30 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox