RE: Primary Keys optional?
- From: "Khemmanivanh, Somckit" <somckit.khemmanivanh@xxxxxxxxxxxxxxxx>
- To: <wbfergus@xxxxxxxx>, <jkstill@xxxxxxxxx>
- Date: Thu, 17 Aug 2006 13:39:24 -0700
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of William B Ferguson
Sent: Thursday, August 17, 2006 1:35 PM
To: jkstill@xxxxxxxxx
Cc: andert@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx;
oracle-l-bounce@xxxxxxxxxxxxx
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@xxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
08/17/2006 02:14 PM
Please respond to
jkstill@xxxxxxxxx
To
andert@xxxxxxxxx
cc
"oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Subject
Re: Primary Keys optional?
On 8/17/06, Stephen Andert <andert@xxxxxxxxx <mailto:andert@xxxxxxxxx> >
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
Other related posts:
