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. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
