Re: Primary Keys optional?

  • From: "Gints Plivna" <gints.plivna@xxxxxxxxx>
  • To: andert@xxxxxxxxx
  • Date: Fri, 18 Aug 2006 00:57:58 +0300

Almost all my professional experience was connected with development
of new apps, mostly DB end. Fortunately from the very beginning I had
some good teachers that understood what referential integrity is and
why it is needed. Initially we used Oracle Designer and since then I
CANNOT imagine application without referential integrity effectively
meaning it contains PKs.
How the heck I could unique filter out a particular row I need? Using
rowid? Or always supplying some awkward criteria containing noone
knows how many predicates? Even for the most temporary tables I've
tried to add some unique number. It is somehow completely naturally at
least for me and usually helps do some debugging as well, I can easily
track a particular row coming from the initial source, existing in
temp and forming target data.
I've participated also in some data conversion projects. Even for
staging tables immediately after copying data from source system we
added a PK just to track how a particular row converts from source
system to target system. It helped to log errors, it helped customer
to find logical mistakes in conversion algorithms, it helped to code
the conversion algorithms. The biggest problem we had was lack of data
integrity, and in particular NULL columns which should be NOT NULL,
infomation chain without FKs, a la classifier values which were filled
manually, concatenated values without algorithmic possibility to
divide them etc. etc. As a result we used more than 2 human years to
analyze requirements and write conversion code for ~40 million rows in
~100 tables.
Since then I'm even more dedicated to enforce referential integrity
(including PKs of course) as much as reasonably possible and it
affected me so much that I even wrote my thoughts about that in
http://www.gplivna.eu/papers/data_waste_or_data_base.htm

Yeahh, I've complained enough, now I feel better :)

Gints Plivna
http://www.gplivna.eu

2006/8/17, Stephen Andert <andert@xxxxxxxxx>:
OK, just starting a new job with more design than I have done in a
while.  Looking into things, I have been noticing that many tables
have no PK.  Some have a unique index, but not all.

When I pointed this out to folks (developers) they shrugged and said
"if you need a PK, then create one".

So my questions are:

1. Is it considered acceptable to have a unique index instead of a pk?

2. What are the circumstances when a table might be allowed to exist
without any sort of primary key or unique index?  (i.e. temp table,
static small table, etc)

Thanks In Advance

--
Stephen Andert
http://spaces.msn.com/andert-news/
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l


Other related posts: