Re: Need for primary keys
- From: amar kumar padhi <amar.padhi@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 01 Jun 2007 16:09:44 +0400
Hi Brain,
we have some applications that do not make use of PKs. The design of
these applications were done years back (oracle x/6/7 era). The data
integrity is maintained using other type of constraints and triggers. I
did an analysis some time back and came up with the following. This may
not be applicable to your application but may give you some pointers why
the design was so done in the past. Comparatively new projects make use
of PK/FK, but then we do base our decision on the normalization level
and whether the application is meant for Data warehousing or online/dss.
Today what we consider as a wrong design may have provided an advantage
in the past. For instance one 10 years old designed application (not
using PK at all) is live at nearly 50 sites and we have not had a single
instance of data integrity problem. Today, the only benefit we see in
using PK in this application is to make use of new dependent features.
Disadvantages that were notices in the older versions:-
- In earlier releases, FK constraints resulted in performance issues.
This was the key reason for not using it then.
- Maintenance issue. It is difficult to copy/delete/refresh data for
test/training setup, ordering of tables always required. We do this
quite frequently.
- indexes are controlled by constraints, disabling a pk means dropping
the index. Be careful of this, re-enabling the constraint might change
the index tablespace causing space and disk spread issues. This has been
taken care of in 9i.
- Constraints require Normalization and there are deviations in design
methodologies. For performance and data accessibility reasons, some
projects prefer not to normalize above 2F, some go till 3F. We
identified some batch processing modules do duplicate data to provide
reporting and performance gain.
For new projects, we realised the importance of integrity as below.
- Oracle has improved on constraints. Performance is not an issue,
haven't heard anyone reporting so. If on 10g, I do not see any issues in
using it, except if the design is not being satisfied or maintenance is
a concern.
- re-engineering is possible and table relationships can be pulled out
from the system, easy to understand complex designs.
- Some features in Oracle rely on PK/FK to be present. Can't use without
these.
- Strict control can be maintained on the design as well as the scripts
being used for maintenance purpose. It will require some extra effort in
the design stage though.
Thanks!
amar
BLock@xxxxxxxx wrote:
I just started at a new company about 4 months ago and I noticed that
about 4800 of the 7900 tables ( 60% ) do not have primary keys.
Is this a problem that I should bring to management or is it more
based on the business/application needs?
Thanks
Brian Lock
DBA - East Campus
ex: 22341
--
//www.freelists.org/webpage/oracle-l
Other related posts:
