Re: Are primary keys always needed?

From: Simon Hedges <shedges_at_hhhh.freeserve.co.uk>
Date: Fri, 21 May 1999 18:46:54 +0100
Message-ID: <7i4616$e3$1_at_news8.svr.pol.co.uk>


Rene Pezo wrote:

>I believe that all tables in a relational database should have primary
>keys. At least in theory, that's how is supposed to be.

Yup

>So, I need some practical examples (let's not talk theory here), as
>answers to the following questions:
>
>1. Are there any cases in which a primary key is not required for a
>table? Or, Is there any good justification for having a table with NO
>primary key?

Yes - we create Audit Tables. Each time a table is updated, its values are copied into an audit table which is of the same format as the main table, but with an Audit ID (completed by using a sequence), the datetime the change was made, and the username of the person who made the change.

We could add a primary key to this consisting of the Audit ID, or a combination of the Key of the source table and the datetime and username. However, there just seems no point. We have no integrity problems (the table only ever being updated from the source table). In fact, we also leave every column on this table as optional.

This is the only time that we have keyless tables.

>2. If a data model as some tables with no primary keys, does this mean
>that the model is NOT a GOOD data model, or is NOT AS GOOD AS it could
>be?

Having no primary key is a bit like having one to one relationships: there CAN be sensible reasons for it, but they'd better be bloody good ones!

Simon Hedges
Gloucester
UK Received on Fri May 21 1999 - 19:46:54 CEST

Original text of this message