By definition, a primary key must be both unique and immutable. If you
have attributes in your table with are both unique and immutable, you
can certainly use a natural primary key (primary key based on meaningful
data elements).

In practice, however, it is relatively rare to come across tables that
meet these two criteria. The classic example here is a social security
number as a primary key for a table of employees-- social security
numbers can be reused, people can change their social security number,
and some people don't have a social security number. It is relatively
easy to add a synthetic primary key (a primary key not based on
meaningful data) populated with a sequence to a table, so that is
frequently the easier approach. That also saves you time talking to all
the business users to ensure that an attribute really cannot change and
dealing with changing business conditions that determine that some
attribute is no longer immutable.

Justin Cave
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

-----Original Message-----
From: EffieJennings
[mailto:oracledba-ezmlmshield-x43312559.[Email address protected]
Sent: Sunday, October 31, 2004 5:44 PM
To: LazyDBA Discussion
Subject: Primary Key Best Practices

Hello,

Could you please spell out why it is or is not a good idea to base a
primary key on anything that is meaningful?

Many base primary keys off sequences, others a derivitive of something
unique out of partial information of something meaningful (like
<location>0001, or <asset_type>0001 etc).

What would you use for a primary key value sequence and why?

Thanks,

Effie



--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html


Oracle LazyDBA home page