Re: unique constraint instead of primary key? what

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