Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re[2]: Limits on referential integrity

Re[2]: Limits on referential integrity

From: <dgoulet_at_vicr.com>
Date: Tue, 22 Jan 2002 08:12:08 -0800
Message-ID: <F001.003F6FC4.20020122060030@fatcity.com>

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

Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit

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 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>

<body>
<pre wrap="">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 <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"

href="mailto:DWILLIAMS_at_LIFETOUCH.COM">DWILLIAMS_at_LIFETOUCH.COM</a> wrote:<br> <blockquote type="cite" cite="mid:md5%3A64756D6D79206D657373616765206964">   <pre wrap="">Jared - I wasn't clear, but then again it is Monday. I have a team of<br>inexperienced developers starting a big, new Java application. They
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 Oracle
7,<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:DWILLIAMS_at_LIFETOUCH.COM">&lt;DWILLIAMS_at_LIFETOUCH.COM&gt;</a><br>Sen t by: <a class="moz-txt-link-abbreviated" href="mailto:r! o !
!
ot_at_fatcity.com">root_at_fatcity.com</a><br>01/21/02 01:35 PM<br>Please respond to ORACLE-L<br><br> <br> To: Multiple recipients of list ORACLE-L <a class="moz-txt-link-rfc2396E"
href="mailto:ORACLE-L_at_fatcity.com">&lt;ORACLE-L_at_fatcity.com&gt;</a><br>       
cc: <br>        Subject:        Limits on referential integrity<br><br><br>How
much 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?
<br>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></pre>
  </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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US