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)?
Should I FK the id column from the location table or should I FK the name
column?
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)?
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.)
Thank you all.
Situation:
Two tables - bookstore and location.
Bookstore has info on books and a column for location. So, I use a foreign
key for the location column. For Location, there are set of locations that
are described by _one_ word (downstairs, <city name>, warehouse, shop ...).
Now, the number of locations might change and so might their description word
(city name might become a warehouse name...). But each location name will
always be unique.
Usage:
The use for the tables is the end-user GUI will ask the user for book info,
but will provide a drop down list for the location input. The drop down list
will be filled by a query from table location.
I want it so that if the super DB manager ever changes one of the names in
the location table, the bookstore table will show the new value instead of
the old. Obviously, the GUI will auto update the drop down list.
PS, I gave my support stuff at the bottom so that people will read the
question first and if they can help, they can choose to read the rest of the
post. Again, thx.