Re: Need for primary keys

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_at_dvfs.com 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

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