Re: Is it necessary to have Primary keys?

Lisa M. Lewis <lmg_at_isdsa.pgh.wec.com> wrote in article <339572EE.A9A78BAB_at_isdsa.pgh.wec.com>...
> Could anyone tell me if it is necessary to have Primary Keys defined on
> all tables in your database. Are there any disadvantages to not having
> primary keys defined? This particular database is used only to run
> large batch transactions against for gathering statistics, etcs. I will
> have duplicate records in my table ( except perhaps for the date/time).
> I don't believe it is wise to put a date in a primary key. I'm a little
> concerned because I have always heard that it is necessary for each
> table to have a primary key. Can anyone shed some light on this for
> me??
>

There is the theoretically correct and the practical. Theoretically, you must be able to identify a primary key (maybe the date and time) for each record. How else could you ensure that exactly the same data was not accidentally provided to the database twice? However, there are occasions when actually implementing the primary key does not make sense. Tables under 8 Oracle blocks often perform better if there are no indexes. Oracle always creates indexes for primary keys. Tables which are updated heavily and are rarely read (or are only read via full-table scan) are sometimes left without indexes of any kind.

Primary keys buy you a number of things. They ensure that identical data is not loaded multiple times into your database. They provide fast retrieval of records for which the primary keys are known. They are part of the mechanism for creating parent/child relationships (the other part is the foreign key).

My own experience is that if you cannot identify a primary key for your table, you probably have some more design work to do. But whether you actually implement the primary key on the table is a matter of deciding whether you can (or should) give up the benefits of having a primary key defined, and testing the performance of normal operations with and without the key.

Also, I have used dates as primary keys, and as part of concatenated keys in many large database tables with a great deal of success. I would have a hard time making the generalization that dates don't belong in primary keys, but there may be something in your application that makes them inappropriate for that use. Received on Fri Jun 13 1997 - 00:00:00 CDT