Message-ID: <43EB1A62.1050705@archonet.com>
Date: Thu, 09 Feb 2006 10:33:06 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5 (X11/20051201)
MIME-Version: 1.0
To: gry@ll.mit.edu
Cc: ken@scottshill.com, pgsql-sql@postgresql.org
Subject: Re: unique constraint instead of primary key?  what
References: <20060208210455.5d6f5bf9.gry@ll.mit.edu>
	<1139452463.16438.8.camel@localhost.localdomain>
	<20060209024450.4413718b.gry@ll.mit.edu>
In-Reply-To: <20060209024450.4413718b.gry@ll.mit.edu>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

george young wrote:
> 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:
>>>
>>> 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.

NULL means "unknown", so a UNIQUE constraint on (run, opset_num, NULL) 
probably doesn't do what you want it to.
At its most basic, ('A',1,null) does NOT equal ('A',1,null). It can't do 
so - two unknowns can't be said to be the same.

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

You can have Access "pass through" queries though which does what you want.

-- 
   Richard Huxton
   Archonet Ltd