pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: pgsql-sql@PostgreSQL.org
Subject: Re: Primary vs Unique Index
Date: Tue, 21 Aug 2001 07:39:29 -0700
Message-ID: <web-105825@davinci.ethosmedia.com> (raw)
In-Reply-To: <20010821092618.8413D1A6B@druid.net>

Darcy,

> 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.

To add my .02 here ... I'd prefer it if PostgreSQL required a Primary
Key at table creation, and forced drop-and-recreate if you attempt to
modify the primary key. Frankly, I regard the failure to enforce Primary
Key requirements to be a PostgreSQL bug, even if Tom & Peter don't.

A strong Primary Key feature is absolutely required if we want to later
build more advanced ANSI SQL 92 features into PostgreSQL.  For example,
if we wanted to improve Postgres so that simple views would be updatable
without writing a lot of custom triggers, then all tables must have a
Primary Key as a prerequisite.

Or, some more common problems: If you created a Key-less table, with a
seperate unique index, you'd run into two problems:
1. Any RDBMS or interface features meant to grab the Primary Key would
fail.  An example of this is MS ODBC, which requires a Primary Key to
make a linked table updatable.
2. If someone later modified your unique index to make it not-unique,
you might find yourself with a table with no unique indexes.  In this
circumstance, the database engine has a hard time identifying individual
rows, and will tend to UPDATE/DELETE entire sets of rows based on common
characteristics.

Fabian Pascal, in "Practical Issues in Database Management" talks
further about the importance of primary keys.




______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco







view thread (3+ messages)

Message-ID: <web-105825@davinci.ethosmedia.com>
Permalink:  ../web-105825@davinci.ethosmedia.com/
Also on:    postgresql.org/message-id/web-105825@davinci.ethosmedia.com

 · 

reply

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: josh@agliodbs.com
  Subject: Re: Primary vs Unique Index
  In-Reply-To: <web-105825@davinci.ethosmedia.com>

* 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