| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: PK or not PK
As a data architect/ data modeler/DBA/ and other things people call me, I
have implemented a very large hammer technique for those analysts or
developers that refute the proper use of data modeling techniques. PKs not
only ensure RI, they prevent developers from duping very stupid things in
their code.
Proper data modeling techniques should NEVER be rescinded because developers want them to be.
Thank You
Stephen P. Karniotis
Product Architect
Compuware Corporation
Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: Stephen.Karniotis_at_Compuware.com Web: www.compuware.com -----Original Message----- Sent: Wednesday, May 07, 2003 5:34 PM To: Multiple recipients of list ORACLE-L Subject: RE: PK or not PK
I find any amount of resources used, large or small, to ensure data integrity has a greater ROI for the business than not having data integrity. The small costs you save up front by skimping on relational design will cost the business many times more in support and maintenance.
Currently we have a similar issue with an Application Engineer, the engineers stance on the database is that it should be interchangeable at a moments notice. In other words, treating the database as a black box that only stores data. What most OO engineers fail to realize is that data integrity starts with the data not the application. Applications come and go but the data will continue to be accessed from the database.
Getting back to your question to PK or not PK, you'll be wise if you use PK's on your tables. If you don't use PK's you'll find yourself fighting the never ending battle of data cleanup.
Mark Moynahan
-----Original Message-----
Sent: Wednesday, May 07, 2003 12:07 PM
To: Multiple recipients of list ORACLE-L
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).
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).
The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Karniotis, Stephen
INET: Stephen_Karniotis_at_compuware.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). Received on Thu May 08 2003 - 09:31:46 CDT
 |
 |