Re: unique constraint instead of primary key? what

On Wed, 08 Feb 2006 18:34:22 -0800
Ken Hill <ken(at)scottshill(dot)com> threw this fish to the penguins:

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

Ouch! A good portion of queries will access my 4M row parameter table
in joins with other tables. It sounds like MS access is not workable.
Thanks for the info.

>
> I hope that helps.
>

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