RE: Primary Keys optional?

Could the data validations/verification be done at the App level?

Of course, the developers should have know this if that were the case...

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of William B Ferguson Sent: Thursday, August 17, 2006 1:35 PM
To: jkstill_at_gmail.com
Cc: andert_at_gmail.com; oracle-l_at_freelists.org; oracle-l-bounce_at_freelists.org
Subject: Re: Primary Keys optional?

Trust me on this. Jared made some extremely valid points.

I've just spent TEN years cleaning up a system that was a total mess. No data validation, no data integrity, no primary key enforcement, and everything was designed as just one large table with multiple values per field (and worse, even allowed different delimiters in the same field).

If I was you, I'd try and enable some PK and FK constraints, and if unable to do so (because of bad data), notify management that the production system is in need of some serious re-design, and that the development team:

1. Needs to help ASAP on correcting the data problems on the production instance,
2. Development be trained on how to properly design tables, etc., or
3. Development has their "Create Table", etc. privs taken away, so only a "knowledgable" person can create the data structure, relegating the dev team to purely application development, not data development.

Somewhere along this process (probably before talking to management), I'd head over and talk to the development team and let them know there's some problems (or at least potential problems), and you need their help to correct it and to ensure it won't happen again. It's a team effort, and you have final say-so on what goes into production now, so you need their help to keep things going smoothly. Offer to help when needed on data structure design, etc., so you don't come off as being arrogant or obnoxious, but somebody willing to work with them. Then, if they have a
"bad attitude", it's fine and dandy to let management know of the
issues, and then let management handle it.

There's a couple places you'll need to lay down the law (both to management and development), and the sooner the better, but at least first offer to help with the problem.

You will sleep much better in the long run.

                              Bill Ferguson
           U.S. Geological Survey - Minerals Information Team
                          PO Box 25046, MS-750
                          Denver Federal Center
                         Denver, Colorado 80225
          Voice (303)236-8747 ext. 321     Fax   (303)236-4208
     ~ Think on a grand scale, start to implement on a small scale ~ 

"Jared Still" <jkstill_at_gmail.com>

Sent by: oracle-l-bounce_at_freelists.org

08/17/2006 02:14 PM
Please respond to
jkstill_at_gmail.com

To
andert_at_gmail.com
cc
"oracle-l_at_freelists.org" <oracle-l_at_freelists.org>
Subject
Re: Primary Keys optional?         

On 8/17/06, Stephen Andert <andert_at_gmail.com <mailto:andert_at_gmail.com> > wrote:
So my questions are:

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

Hi Stephen,

unique index != primary key

primary keys require not null columns
unique indexes do not.

If there are no tables with PK, there has been no design effort.

It's more along the lines of "let's throws some columns at some tables and see what sticks"

Without Primary Keys, it is highly unlikely that the database is in 2nd normal form (columns depend on the entire key)

If it isn't in 2NF, then it also cannot be in 3NF, BCNF, 4FN, or any other higher form of NF.

Well, maybe DNF.

It's also very likely that the tables are not in 1NF (repeating groups) address_1, address_2, ...
concatenated strings (heaven forbid)

In short, the data in this database is likely to be a mess.

INSERT, DELETE and UPDATE statements will all be difficult to write. DML will be subject to anomolies.

Data will be inaccurate as well.

I could go on, but I've ranted long enough.

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)

Temp tables: maybe. It depends on how and why they are used.

Small tables: these definitely require a PK. Size does not matter.

If possible, you may want to create PK and FK.

If you are able to do so, the developers will then come knocking at your door, as they will no longer be able to put bad data in the database.

Your challenge is to educate them. :)

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
and Database Design  Bigot






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