Re: unique constraint instead of primary key? what

On Wed, 2006-02-08 at 21:04 -0500, george young wrote:

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

I think I can give you some insights about MS Access to help you. In MS
Access, you can specify a column as a "primary key"; which basically
means the column is indexed and must contain unique values (also, nulls
are not allowed). I have run into problems depending on columns being
"primary key" in MS Access in db apps that receive data in batch file
uploads from other sources (e.g., uploading 1,000+ records into a
table).

Is sounds like your requirement to use MS Access for ad-hoc queries
means that you will have some users that want to access the database
with MS Access as a "front-end" client tool. If that is the situation,
then you don't need to worry about the structure of the table as MS
Access relies on ODBC for this. You may also want to communicate to the
end users that MS Access is not a client-server tool; in other words,
all of the records are transferred from the server to the client's box
and then the query is executed.

I hope that helps.