From: Richard Huxton <dev@archonet.com>
To: gry@ll.mit.edu
Cc: ken@scottshill.com
Cc: pgsql-sql@postgresql.org
Subject: Re: unique constraint instead of primary key? what
Date: Thu, 09 Feb 2006 10:33:06 +0000
Message-ID: <43EB1A62.1050705@archonet.com> (raw)
In-Reply-To: <20060209024450.4413718b.gry@ll.mit.edu>
References: <20060208210455.5d6f5bf9.gry@ll.mit.edu>
<1139452463.16438.8.camel@localhost.localdomain>
<20060209024450.4413718b.gry@ll.mit.edu>
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
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: dev@archonet.com, gry@ll.mit.edu, ken@scottshill.com
Subject: Re: unique constraint instead of primary key? what
In-Reply-To: <43EB1A62.1050705@archonet.com>
* 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