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 <[EMAIL PROTECTED]>
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
[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 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 <[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]
>
--------------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:[EMAIL PROTECTED]";>[EMAIL PROTECTED]</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:[EMAIL PROTECTED]";>[EMAIL PROTECTED]</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:[EMAIL PROTECTED]";><[EMAIL PROTECTED]></a><br>Sen
t by: <a class="moz-txt-link-abbreviated" href="mailto:r!
o !
!
[EMAIL PROTECTED]">[EMAIL PROTECTED]</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:[EMAIL PROTECTED]";><[EMAIL PROTECTED]></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:[EMAIL PROTECTED]";>[EMAIL PROTECTED]</a><br><br></pre>
</blockquote>
<br>
</body>
</html>
--------------070606010707000609020708--
--------------020102080806060304030001--
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: orantdba
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
