From: george young <gry@ll.mit.edu>
To: pgsql-sql@postgresql.org
Subject: unique constraint instead of primary key? what disadvantage(ODBC usage)?
Date: Wed, 8 Feb 2006 21:04:55 -0500
Message-ID: <20060208210455.5d6f5bf9.gry@ll.mit.edu> (raw)
[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)
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: gry@ll.mit.edu
Subject: Re: unique constraint instead of primary key? what disadvantage(ODBC usage)?
In-Reply-To: <20060208210455.5d6f5bf9.gry@ll.mit.edu>
* 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