Am Freitag, 23. Juli 2004 12:02 schrieb Kenneth Gonsalves: > ok, i'll rephrase the whole thing: > > i have a master table with two fields: > id serial unique > name varchar not null (and will be unique) > > i always make 'name' the primary key, and since it is the primary key, i > dont explicitly specify it as unique, and after postgres 7.3 came out, i > have added the 'unique' constraint to the 'id' > > on looking at the gnumed schema, i saw that although 'name' was unique, the > serial key, 'id' was made the primary key. So i wondered why and whether > there were advantages in doing it this way.
Does your question relates to surrogate vs natural keys discussion? I made some researches a few months ago and read a lot including: http://www.intelligententerprise.com/print_article_flat.jhtml?article=/030320/605celko1_1.jhtml http://www.winnetmag.com/Articles/Print.cfm?ArticleID=5113 http://www.dbpd.com/vault/9805xtra.htm http://www.sapior.com/Resources/Surrogate_Keys/surrogate_keys.html http://www.bits-pilani.ac.in/dlp-home/online/studymat/sszg515/lecture3.htm http://www.bcarter.com/intsurr1.htm i decided to use natural keys wherever possible and i have many primary keys spanning up to 4 attributes. And it works really fine. Performance is great, the schema is easy to use and i am so glad to use the natural key approach. Writing SQL queries and php code is much easier! By now i try to avoid surrogate keys (like with SERIALs datatype) wherever possible. Most articles advocate surrogate keys and at first it looks like an advantage in a web environment because selecting and transmitting a multi-column primary key in a form field ist very difficult. Imagine a <select> element, but you have only one value to be returned. My trick here is to have the primary keys used in the select element saved in a session array and using the session array index as a select element value. But the strongest argument for me is: All candidate keys have to be unique anyway. And postgresql builds an index anyway for every UNIQUE key, because thats the way postgresql checks uniqueness. So why add another artifical key with another index when you can use the one which is given anyway. Think of usergroups identified by name and members which are identified by user groups name and email adress, then you've got the pseudo schema create table usergroups ( ug_name text, CONSTRAINT uq_ug UNIQUE (ug_name) ); create table members ( ug_name text, mb_email text, CONSTRAINT uq_mb UNIQUE (ug_name, mb_email), CONSTRAINT fk_ug_name FOREIGN KEY ug_name REFERENCES usergoups (ug_name) ); so you have to indexes uq_mb and uq_ug anyway. So why dont use them as Primary Keys?? With two more attribute for a surrogate key like ug_id SERIAL PRIMARY KEY in table usergroups and mb_id SERIAL PRIMARY KEY you have additional 4 bytes to store for each table row and one more index for each table. So my conclusion is: i dont see any benefit in using surrogate keys. But this must be wrong because so many people are using and advocating surrogate keys. They might only be useful in circumstances where no natural key is given. kind regards, janning ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
