Re: A problem in SQL (a real challenge)

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

David Cressey, consultant
david_at_dcressey.com

Raimundo Lozano wrote in message <391A66CE.F4CC323B_at_medicina.ub.es>...
>
>
>Thomas Muller wrote:
>
>> In general I agree, hence the statement "Do note that a table without a
>> primary key violates 1NF, hence should
>> generally be avoided." in my last reply. --
>
>It's obvious that a table have to have a primary key, but I don't agree
 that a
>table without a primary key violates 1NF. The definition of 1NF is that the
>table has no repeating groups and that all columns are scalar, the value of
 a
>column in a row is atomic, is not an array for example. As Joe Celko say in
 his
>book, SQL not support arrays and structured data but you may "fake it" in
 SQL
>putting a group of columns in wich all the members of the group represent
 the
>same attribute in the table. But I thing this is independent of the concept
 of
>primary key.
>
>If I'm wrong and I've misunderstood any concept, please give me some light.
>
>Raimundo Lozano
>
Received on Thu May 11 2000 - 00:00:00 CEST