Home » Other » General » About the systematic use of primary keys (Oracle 10g)
About the systematic use of primary keys [message #343871] Thu, 28 August 2008 06:50 Go to next message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
Hello everyone,

I have been brought throughout my career to work for two different employers on two different DB platforms, namely MySQL and Oracle. I have had the opportunity to witness two different approaches to database design policies regarding the use of primary keys and I wanted to question if either was The Best or if each in a sense had a point while both were inspired from the relational database model.

The Plot:

The MySQL era.
When I was working with MySQL, the policy there for table creation was the systematic referencing of records with a numeric primary key that auto-incremented as records were added. This was cool since you defined all that at table creation , then forgot about it. Also, in programming, you only had to reference matching records based on the primary/foreign-key pair...as easy as 1-2-3.

The Oracle era.
With Oracle, things are a bit different since if I were to implement this, I would have to split the job three-fold :
1] Creation of a sequence
2] Definition of unique constraint on field
3] Management of "auto-increment" feature via an BEFORE INSERT Trigger

Feasible but a hell of a few more tasks to be done. Moreover, as I explained, the other "Oracle-based" employer does not have this practice. Primary keys and sequences are used sparingly and indexes handled in a different tablespace (for performance reasons I presume)

Required

My point so far as a newbe Forms developer was to ask why primary keys were not systematic since that would have greatly simplified things in my daily life. In fact there came times when I had to juggle with many things to produce valid and consistent code as far as data manipulation was concerned.

In your enlightened opinion, do you believe either approach is better or do they in fact stand for their own purposes?

regards,
Didier
Re: About the systematic use of primary keys [message #343895 is a reply to message #343871] Thu, 28 August 2008 07:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not using natural, functional, business primary key?

Read AskTom surrogate Key thread.

Regards
Michel
Re: About the systematic use of primary keys [message #344097 is a reply to message #343871] Thu, 28 August 2008 14:56 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
didiera wrote on Thu, 28 August 2008 13:50
1] Creation of a sequence
2] Definition of unique constraint on field
3] Management of "auto-increment" feature via an BEFORE INSERT Trigger

Feasible but a hell of a few more tasks to be done.

Nonsense. This is a one time only thing, so it's not "a hell of a few more tasks to be done".
My idea of a good db-design consists of ID-fields for each and every table (_not_ prefixed with the table alias), filled by triggers from sequences. These will function as Primary Keys.
These PKs will be used in referencing keys; in the referencing table the column_name will be created from the table-alias of the PK-table - underscore - ID. (e.g. table department (alias dpt) has PK of ID. Table employee references it with column DPT_ID)

Business logic requires Unique keys. If you take a very good look at requirements, they seldomly require Primary Keys, so I consider PKs as more of a technical thing.
Previous Topic: dba questions
Next Topic: what is written in oracle?
Goto Forum:
  


Current Time: Thu Mar 28 18:24:23 CDT 2024