On Sat, May 22, 2004 at 12:42:53 -0400,
Vamsikrishna Mudrageda <gte181u@prism.gatech.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.