Re: Limits on referential integrity

[orantdba]

Hi Dennis,

Agreed this was not the developers fault,  it was the DBA's!  I don't blame
this on RI being handled by constraints, but on a DBA that doesn't understand
the consequences, the resulting table level lock could have also been a problem :-).  
BTW, if RI had been handled via the application they would have had the same
problem.

John

[EMAIL PROTECTED] wrote:

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____________________
Subject:    Re: Limits on referential integrity
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 
constraint
s 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%3
A64756D6D79206D657373616765206964">
  <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<b
r>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>design
ing
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--