Re: Are primary keys always needed?

I believe that Simon is essentially correct. The problem here is a disconnect between logical and physical modeling. In every "entity" you MUST have a primary key. To not know what uniquely defines the rows is a recipe for disaster. The question is whether you need or want an "Oracle primary key" on a "table" is one of design standards.
A primary key automatically gives you an index, with its associated overhead.
If you want to have a referential integrity constraint off the table then you MUST have a primary key.

Many relational people get VERY upset if they see a table with no primary key.
There is an industry standard that all tables get primary keys. Designer will build them for you for every table if you don't specify them yourself.

My suggestion is to use primary keys for all tables. It costs you little and may save you headaches in trying to justify your design standards to others.

--
Paul Dorsey
Dulcian, Inc.
www.dulcian.com
212 595 7223
Simon Hedges wrote in message <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

>

>