Date: Wed, 8 Feb 2006 21:04:55 -0500
From: george young <gry@ll.mit.edu>
To: pgsql-sql@postgresql.org
Subject: unique constraint instead of primary key?  what disadvantage(ODBC
	usage)?
Message-Id: <20060208210455.5d6f5bf9.gry@ll.mit.edu>
Reply-To: gry@ll.mit.edu
Organization: MIT Lincoln Laboratory
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

[PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1]
I'm designing a completely new schema for my database.  A major
criterion is that it facilitate ad-hoc queries via MS-access, excel and
OpenOffice, presumably with ODBC.

My question regards the use of UNIQUE constraints instead of PRIMARY
KEY's on some tables.  Both result in an index on the specified tuple
of fields, so I presume query performance shouldn't be much different.

Using UNIQUE constraints seems to let me better match the natural
structure of my data.  A 'run' contains a sequence of 'opsets'.
Each opset contains a sequence of (a few) 'step's.

   run-foo
      opset-1
         step-1
         step-2
      opset-2
         step-1

So the 'steps' table is logically indexed by (run, opset_num, step_num).
But some opsets are not in runs, and some steps are not in opsets, so
I would have step.run be null in some cases, likewise step.opset_num.

Null values mean I can't use these fields in a primary key, so I
propose to use UNIQUE constraints instead.

What am I losing by not using PRIMARY KEYS?  Will ODBC clients have
difficulty dealing nicely with the database?  Will the planner produce
lousy query plans?  Will Edgar Codd haunt my dreams?

-- George Young


-- 
"Are the gods not just?"  "Oh no, child.
What would become of us if they were?" (CSL)