Hi Dennis, Just my opinion but I tried to follow these rules as a DBA.
1. If the business rule can be implemented with pk, fk, unique or check constraints I do it as such 2. If the business rule can be implemented as a trigger I code it as a trigger 3. If none of the above, I implement as a stored procedure and try to insure that every developer uses this procedure. Occasionally I have heard the "performance discussion" in regards to constraints. In 5 years of consulting I have never had constraints be THE problem. But if I was a developer that had written some of the awful SQL I have seen, I might have tried. Constraints do put a premium on error checking by the application on inserts/updates. Hope this helps, John
[EMAIL PROTECTED] wrote:
Jared - I wasn't clear, but then again it is Monday. I have a team of
inexperienced developers starting a big, new Java application. They have a
good, experienced data model consultant helping them create the data model.
They are eager to include referential integrity. So eager it has me a little
worried. My question: "Is there too much of a good thing?". In Oracle 7,
sometimes sites would remove RI to ensure good performance (we are starting
this project on Oracle9i). Has anyone encountered problems with too many
constraints? Any guidelines you use with developers? Thanks.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-----Original Message-----
Sent: Monday, January 21, 2002 4:16 PM
To: Multiple recipients of list ORACLE-L
I would be you lunch that what they are implementing in their
code is not actually R I. They may be implementing code to
ensure things get inserted in the right order, and that child rows
have a parent.
This is a very weak form of RI. Oracle is very good at implementing
RI, and it is not dependent on an application. RI in the database
is the route to choose unless there is some good reason not to.
RI in the database will prevent orphaned data created through
updates, deletes or even ( gasp! ) bugs in the app.
Programmers tend to dislike RI in the database because it
forces them to maintain data integrity in a transaction. This is
not a bad thing, it just forces them to have a good understanding
of their transactions.
Point out to them that it is less code to write as well. :)
Jared
DENNIS WILLIAMS <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/21/02 01:35 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: Limits on referential integrity
How much referential integrity should be implemented in Oracle? We are
starting a large new Java project. Our current applications keep their
referential integrity inside their own dictionary, so I haven't had to
deal
much with referential integrity recently. Can there be too much of a good
thing? What guidelines do you tend to use? At this point the developers
are
designing the data model so they are busily linking all the little boxes.
My
attitude at this point is "implement what you've got and if there are
performance problems we'll deal with them when they arise". Can anyone
give
me a better motto?
Thanks.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
