Received: from druid.net (druid.net [216.126.72.98]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f7L9QIP16554 for ; Tue, 21 Aug 2001 05:26:18 -0400 (EDT) (envelope-from darcy@druid.net) Received: by druid.net (Postfix, from userid 1000) id 8413D1A6B; Tue, 21 Aug 2001 05:26:18 -0400 (EDT) Subject: Re: Primary vs Unique Index To: GonzoRock@Excite.com (Gonzo Rock) Date: Tue, 21 Aug 2001 05:26:18 -0400 (EDT) Cc: pgsql-sql@postgresql.org Reply-To: pgsql-sql@PostgreSQL.org In-Reply-To: <3.0.5.32.20010820234510.009c5530@postoffice.pacbell.net> from "Gonzo Rock" at Aug 20, 2001 11:45:10 PM X-Mailer: ELM [version 2.5 PL5] MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Message-Id: <20010821092618.8413D1A6B@druid.net> From: darcy@druid.net (D'Arcy J.M. Cain) X-Archive-Number: 200108/264 X-Sequence-Number: 4511 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 | 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.