Re: A problem in SQL (a real challenge)

"David Cressey" <david_at_dcressey.com> wrote:

>Whether I agree with you or not depends on the interpretation of "has a
>primary key".
>I know this sounds like a politician, but I'm not trying to be evasive.
>
>A table might have the following features, in the abstract: no two rows
>will ever be identical,
>according to the business rules. Of all the columns, some are mandatory.
>That is, NULLS are
>not allowed. Some subset of the mandatory columns would make an adequate
>primary key,
>according to the business rules. And yet, no primary key has been declared
>to the database.
>
>Many people would say that such a table has no primary key. And for
>purposes of discussing things at the DBMS level, I would agree. However,
>if the question is "is this table in 1NF?" I would say that, in this
>context, the table has a possible primary key, and is therefore in 1NF.
>
>
>Now, let me slip into attitude: If a perfectly logical primary key exists,
>and you don't declare it, now
>you are vulnerable to an operational error that somehow ends up loading the
>same valid incoming data twice. Wouldn't it be better to have the DBMS
>defend the database against this sort of abuse? As

     What if it is not abuse? I write/support a client billing system where it is possible to have more than one transaction with what might be considered the primary key. These are generally dealt with in a group. It is usually irrelevant to my boss which order they are listed in, but a transaction number can be entered to force a particular order. Whether the group has one transaction in it or more is irrelevant: all of the transactions will be consolidated for the invoice.

>far as the performance hit of declaring a primary key (and getting the
>corresponding index), my answer is: deal with it. 95% of the time,
>you'll get the performance back in lookups and/or joins on the table anyway.
>In the other 5% of the cases, the architecture of your application is
>probably not the best, anyway.
>
>Hope this helps.

     I generally go with at least one key that is unique, but in the case above, it doesn't seem warranted.

[snipped previous]

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:

     I have preferences.
     You have biases.
     He/She has prejudices.