Re: PK or not PK

John,

I tend you agree with you to some degree, that is, not ALL tables need a primary key - most do but there are a couple of instances where it is beneficial not to have them or to defer their creation/maintenance. I do NOT believe that your example of the parent child relationship is one of these instances.

If you take a broad classification of the tables in an application they tend to fall into those tables that are core to your business, and those tables that are peripheral to the business but required by the application. For example, the table CUSTOMER would be core to the business while a table used to temporary load customer data to stage it in and out of the database is peripheral. I believe the tables that are core to your business should always have a primary key to maintain integrity and in general performance (primary key being a index itself). However the peripheral tables are candidates for not having a primary key.

One application I worked on had a set of tables whose only purpose in life was to help stage data into and out of the core business tables. The data in the staging tables had the following properties:

1. it was temporary, that is it never existed longer than the time needed to load the data
2. we always read from the first row to the last row.
3. there was always around the same amount of data.

In our case the staging tables did not have primary keys and this improved our load performance dramatically.

Just one word one the use of the ROWID. The ROWID contains amongst other things, the address of the row on disk - which could change depending on the partitioning options and the various online re-organization commands that might be executed. I am not entirely convinced that if you read a ROWID at time t1 that the ROWID will be the same at t2 for any given row.

Cheers,
Craig.

At 11:07 AM 7/05/2003 -0800, John Carlson wrote:
>I am searching for arguments for NOT having a primary key. All I seem to
>find are discussions about how to use artificial or surrogate keys but
>nothing about not needing them at all.
>
>For example, a one to many relationship. Parent table has a primary key
>but the child table does not have any natural unique key. You create a
>foreign key with a non-unique index on the child table and that is all you
>really need. Now, the application queries the parent child relationship
>and lists rows for a given parent. The user then selects one of the rows
>and the program needs to select that record for update. This can easily
>be accomplished with rowid; however, our developers use a tool which ( in
>an effort to be a generic tool ) cannot handle oracle's rowid and insists
>on having a primary key on the child table.
>
>My argument is that this is a total waste of resources. It takes overhead
>to maintain an index and it also consumes disk space and serves no useful
>purpose. Their argument is that they want their code to be independent of
>the database so we could switch from oracle to any other database without
>changing any code. I say that is ridiculous because you always have to
>'tune' your code to work efficiently and each one has their own unique
>requirements. Another argument is that in a true relational database, all
>tables must have a primary key. I don't know anyone who has a 'true'
>relational database just like you are supposed to use third normal
>form. But, in order to help queries be more efficient, we often back off
>to second normal form. It just isn't realistic or practical in the real
>world to conform to all the 'rules'.
>
>Can anyone help me out or am I fighting a loosing battle?
>
>TIA,
>John Carlson
>www.cj.com
>Santa Barbara, CA. USA
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: John Carlson
> INET: jcarlson_at_cj.com
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Craig Munday
  INET: cmunday_at_bigpond.net.au

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).