From: darcy@druid.net
To: GonzoRock@Excite.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Primary vs Unique Index
Date: Tue, 21 Aug 2001 05:26:18 -0400 (EDT)
Message-ID: <20010821092618.8413D1A6B@druid.net> (raw)
In-Reply-To: <3.0.5.32.20010820234510.009c5530@postoffice.pacbell.net>
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 post table 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 duplicated with 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: darcy@druid.net, GonzoRock@Excite.com
Subject: Re: Primary vs Unique Index
In-Reply-To: <20010821092618.8413D1A6B@druid.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox