Re: Primary vs Unique Index

Thus spake Gonzo Rock
> Why would one need a Primary Key... which can only be declared at table creation if one can create a Unique Index
posttable creation?
 
> 
> ie: I deleted my primary key... is that a big deal? What's the purpose of the Primary Key if it's function can be
duplicatedwith another Unique Index?
 

It has to do somewhat with database theory.  There is a basic distinction
between a unique index and a primary key.  Ideally, the primary key should
never change but a unique key can as long as the new value is also unique.
PostgreSQL doesn't enforce this (I think it should) but a good database
design will include primary keys that can remain constant.

One thing that PostgreSQL allows is for a program to query a database
and determine which key in a table is the primary key.  You can use
this in user programs to make decisions like that suggested above.  I
use it in PyGreSQL (Classic) to find records based on a dictionary of
values.  For example, let's say I have just loaded an address record
into a dictionary and I would like that dictionary to include the full
province name.  I have a province table that allows that lookup.
 db.get('province', dict)

The get method looks at province and determines that the primary key
is 'prov' so it looks up that key in the dictionary and uses it to
load in (and merge) the province record.  If you are familiar with
a 4GL such as Progress you may recognize this soul brother of the
above statement.
 FIND province OF address.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.