Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re[2]: Limits on referential integrity
John,
I have only seen one situation where referential integrity has caused a problem that the developer could not prevent. That case involved a foreign key with the 'on delete cascade' option turned on and the key column in the child table was NOT indexed. OH, BTW the child table was well lets just say VERY large (2 billion rows).
Dick Goulet
____________________Reply Separator____________________ Author: orantdba <orantdba_at_netscape.net> Date: 1/22/2002 5:40 AM --------------020102080806060304030001
Hi Dennis,
Just my opinion but I tried to follow these rules as a DBA.
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 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.
>dwilliams_at_lifetouch.com
>
>
>-----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 RI. 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 <DWILLIAMS_at_LIFETOUCH.COM>
>Sent by: root_at_fatcity.com
>01/21/02 01:35 PM
>Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> 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.
>dwilliams_at_lifetouch.com
>
--------------020102080806060304030001
Content-Type: multipart/related;
boundary="------------InterScan_NT_MIME_Boundary"
--------------InterScan_NT_MIME_Boundary
Content-Type: multipart/alternative;
boundary="------------020102080806060304030001"
boundary="------------070606010707000609020708"
--------------070606010707000609020708 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit <html> <head> </head>
Just my opinion but I tried to follow these rules as a DBA.
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 <img src="chrome://editor/content/images/smile_n.gif" alt=":-)" class="moz-txt-smily" height="19" width="19" align="Center">. Constraints do put a premium on error checking by the application on inserts/updates.
Hope this helps,
John</pre>
<br> <br> <a class="moz-txt-link-abbreviated"
have a<br>good, experienced data model consultant helping them create the data model.<br>They are eager to include referential integrity. So eager it has me a little<br>worried. My question: "Is there too much of a good thing?". In Oracle7,<br>sometimes sites would remove RI to ensure good performance (we are starting<br>this project on Oracle9i). Has anyone encountered problems with too many<br>constraints? Any guidelines you use with developers? Thanks.<br>Dennis Williams<br>DBA<br>Lifetouch, Inc.<br><a class="moz-txt-link-abbreviated" href="mailto:dwilliams_at_lifetouch.com">dwilliams_at_lifetouch.com</a><br><br><br>--- --Original Message-----<br>Sent: Monday, January 21, 2002 4:16 PM<br>To: Multiple recipients of list ORACLE-L<br><br><br>I would be you lunch that what they are implementing in their<br>code is not actually R I. They may be implementing code to <br>ensure things get inserted in the right order, and that child rows<br>have a parent.<br><br>This is a very weak form of RI. Oracle is very good at implementing<br>RI, and it is not dependent on an application. RI in the database<br>is the route to choose unless there is some good reason not to.<br><br>RI in the database will prevent orphaned data created through <br>updates, deletes or even ( gasp! ) bugs in the app.<br><br>Programmers tend to dislike RI in the database because it<br>forces them to maintain data integrity in a transaction. This is<br>not a bad thing, it just forces them to have a good understanding<br>of their transactions.<br><br>Point out to them that it is less code to write as well. :)<br><br>Jared<br><br><br><br><br><br><br><br>DENNIS WILLIAMS <a class="moz-txt-link-rfc2396E"
href="mailto:ORACLE-L_at_fatcity.com"><ORACLE-L_at_fatcity.com></a><br> cc: <br> Subject: Limits on referential integrity<br><br><br>Howmuch referential integrity should be implemented in Oracle? We are<br>starting a large new Java project. Our current applications keep their<br>referential integrity inside their own dictionary, so I haven't had to <br>deal<br>much with referential integrity recently. Can there be too much of a good<br>thing? What guidelines do you tend to use? At this point the developers <br>are<br>designing the data model so they are busily linking all the little boxes. <br>My<br>attitude at this point is "implement what you've got and if there are<br>performance problems we'll deal with them when they arise". Can anyone <br>give<br>me a better motto?
</blockquote> <br> </body> </html>
--------------070606010707000609020708--
--------------020102080806060304030001--
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: orantdba INET: orantdba_at_netscape.net Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). --------------InterScan_NT_MIME_Boundary-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: dgoulet_at_vicr.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Tue Jan 22 2002 - 10:12:08 CST