Re: Limits on referential integrity

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

DWILLIAMS_at_LIFETOUCH.COM wrote:

  Jared - I wasn't clear, but then again it is Monday. I have a team ofinexperienced developers starting a big, new Java application. They have agood, experienced data model consultant helping them create the data model.They are eager to include referential integrity. So eager it has me a littleworried. My question: "Is there too much of a good thing?". In Oracle 7,sometimes sites would remove RI to ensure good performance (we are startingthis project on Oracle9i). Has anyone encountered problems with too manyconstraints? Any guidelines you use with developers? Thanks.Dennis WilliamsDBALifetouch, Inc.dwilliams_at_lifetouch.com-----Original Message-----Sent: Monday, January 21, 2002 4:16 PMTo: Multiple recipients of list ORACLE-LI would be you lunch that what they are implementing in theircode is not actually R I. They may be implementing code to ensure things get inserted in the right order, and that child rowshave a parent.This is a very weak form of RI. Oracle is very good at implementingRI, and it is not dependent on an application. RI in the databaseis 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 itforces them to maintain data integrity in a transaction. This isnot a bad thing, it just forces them to have a good understandingof their transactions.Point out to them that it is less code to write as well. :)JaredDENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>Sent by: <a class="moz-txt-link-abbreviated" href="mailto:ro ! !
ot_at_fatcity.com">root_at_fatcity.com01/21/02 01:35 PMPlease respond to ORACLE-L To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc: Subject: Limits on referential integrityHow much referential integrity should be implemented in Oracle? We arestarting a large new Java project. Our current applications keep theirreferential integrity inside their own dictionary, so I haven't had to dealmuch with referential integrity recently. Can there be too much of a goodthing? What guidelines do you tend to use? At this point the developers aredesigning the data model so they are busily linking all the little boxes. Myattitude at this point is "implement what you've got and if there areperformance problems we'll deal with them when they arise". Can anyone giveme a better motto? Thanks.Dennis WilliamsDBALifetouch, Inc.dwilliams_at_lifetouch.com             Received on Tue Jan 22 2002 - 08:59:19 CST