Date: Thu, 9 Feb 2006 02:44:50 -0500
From: george young <gry@ll.mit.edu>
To: ken@scottshill.com
Cc: pgsql-sql@postgresql.org
Subject: Re: unique constraint instead of primary key?  what
Message-Id: <20060209024450.4413718b.gry@ll.mit.edu>
In-Reply-To: <1139452463.16438.8.camel@localhost.localdomain>
References: <20060208210455.5d6f5bf9.gry@ll.mit.edu>
	<1139452463.16438.8.camel@localhost.localdomain>
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

On Wed, 08 Feb 2006 18:34:22 -0800
Ken Hill <ken@scottshill.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)