unique constraint instead of primary key? what disadvantage(ODBC usage)?

From: george young <gry(at)ll(dot)mit(dot)edu>
To: pgsql-sql(at)postgresql(dot)org
Subject: unique constraint instead of primary key? what disadvantage(ODBC usage)?
Date: 2006-02-09 02:04:55
Message-ID: 20060208210455.5d6f5bf9.gry@ll.mit.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

[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)

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Ken Hill 2006-02-09 02:06:10 Re: Syntax for "IF" clause in SELECT
Previous Message Ken Hill 2006-02-09 01:53:59 Column Index vs Record Insert Trade-off?