Re: Data Constraints Vs Application Constraints

sparky wrote:
>
> As a consequence I am compiling a list of arguements for enforcing
> data integrity within the database (as well as within the app) and
> would be interested in anyone's opinions and/or experiences within a
> similar situation, and what they found to be the key arguements which
> won the business round.
>

Here are some thoughts:

1. Adding constraints into the DBMS after the fact is a big job, because the app programs for sure don't always insert/delete the rows into/from the database in the correct order. All the programs have to be visited, and insert/delete orders checked/changed. And the whole mess has to be thoroughly tested. I would advise against changing an existing application.

(I must confess that I am not very familiar with the "deferred constraints"
of Oracle. If they work, they might solve this problem.)

2) It might not be so obvious why constraints are a good thing, expecially
since they cause trouble when inserting test data. You have to either disable the constraints, or laborously insert the data in the correct order - no trivial task, if you have > 50 tables, especially with cycles!

And anyway, you still have to check the constraints "yourself" in the app program, because it is not very easy to rely on the SQL-messages of the DBMS to provide meaningfull error messages to the end user.

3) Reasons for favouring referential integrity constraints:

• "free" documentation of database, easy to reverse engineer and produce diagrams
• safeguards against *some* application errors
• semantic optimizing done by DBMS'es, i.e. by knowing the constraints the optimizer can make better decisions when joining tables
• reporting tools can use the referential integrity constraints to help end users produce reports

In summary, I am all for ref.integrity constraints, but adding them to a live application is something I would not dare do.

Regards,
Lauri Pietarinen Received on Sun Mar 13 2005 - 07:49:44 CET