Re: DB design opinions - Foreign Key usage

On Sat, May 22, 2004 at 12:42:53 -0400,
Vamsikrishna Mudrageda <gte181u(at)prism(dot)gatech(dot)edu> wrote:
> Intro:
> Hi all, my name is Vams, and I am fairly new to postgresql and totally new to
> mailing lists, so please bare with me. I have used hypersonic sql and mysql,
> and now I am trying out postgresql... and so far, very impressive. GJ dev
> team.
>
> Problem:
> For the location table, should I use two columns (ID PK, name) or just one
> column (name PK)?

It generally isn't a good idea to make a name the primary key unless you
are very sure the names aren't going to change.

> Should I FK the id column from the location table or should I FK the name
> column?

You should refer to the primary key when making foreign key references to
the table.

> Does postgresql keep a reference when using a FK (like pointers in C) or
> does it actually make a copy and store it (like when C passes a copy in a
> function call)? If its the later, is there a way to reference a value in
> postgresql (and have it auto-dereference during a query)?

The key is stored in the table. There are special triggers that make sure
that the key is actually in the referenced table.

> Finally, off topic, is there anyway (other than triggers) to keep the user
> from inserting into the ID column when a sequence already takes care of it?
> (I don't want someone adding id=5, name='blah' and when the sequence reaches
> 5, the insert will return error: can't have duplicates.)

I think you can get this to happen with rules, but a before trigger is the
more normal way to modify rows being inserted. Note you will want to do
something for updates as well, since you can change the value of a primary
key in an update.