Re: Primary Keys optional?

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_at_gmail.com>:
> 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/
> --
> http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l