Re: satisfies algorithm

"David Portas" <REMOVE_BEFORE_REPLYING_dportas_at_acm.org> wrote in message news:vJ6dnRSku8yechfVnZ2dneKdnZydnZ2d_at_giganews.com...
> "Brian Selzer" <brian_at_selzer-software.com> wrote in message
> news:92kik.14667$cW3.1512_at_nlpi064.nbdc.sbc.com...
> >
> > Yes. Normalize. A schema that is in BCNF does not have any nontrivial
> > functional dependencies where the determinant is not also a key. Where
> > there is a key, there should also be a unique index of some sort, making
> > it impossible for there to be two tuples with the same determinant.
> >
>
> Unique indexes have nothing to do with keys. A key is a logical construct
> whereas an index is merely one possible physical structure used by some
> DBMSs. A key does not require an index.
>

True in theory, but very misleading to any newbies who may read it.

A declared primary key with no index to back it up is generally a performance nightmare.
Enforcing uniqueness of keys without an index requires a full table scan. For this reason most DBMS builders provide an automatic index when a primary key is declared.

To be sure, the concept of "key" and "primary key" are different concepts. Also a key would be a key, even if it were undeclared.

Relying on a key that is not declared either via a PRIMARY KEY constraint or UNIQUE and NOT NULL constraints, is an invitation to database damage done by a rogue process, possibly as the result of carelees programming coupled with data.

Newbies need to understand all this.

Having said this, I repeat that you original comment is correct in theory. And this is, after all, the database theory newsgroup.

> --
> David Portas
>
>
Received on Mon Jul 28 2008 - 18:58:50 CEST