Operating System - HP-UX
1748111 Members
3456 Online
108758 Solutions
New Discussion юеВ

Re: Best Practices & Integrety Constraints

 
SOLVED
Go to solution
Scott Williams_5
Frequent Advisor

Best Practices & Integrety Constraints

System: Clustered rp7410's running Oracle 9.2 on HPUX 11i.

I'd appreciate hearing opinions from DBA's on the use of integrety constraints within the database. As an application developer for 8 years and a DBA for 3, integrity was always enforced within the application. Now, one of our new developers is pushing for adding these constraints within the database. Any gotcha's out there?

Oracle Concepts says they're a good thing, but...I'd like some real-world opinions.

Thanks
5 REPLIES 5
Steven E. Protter
Exalted Contributor

Re: Best Practices & Integrety Constraints

We enforce contraints through key relationships in the database.

If there is a one to one relationship between tables, the database takes care of that. Same thing with one to many relationships.

We spend a lot of time designing the data tables to make sure our application works right. I'm not the dba, but his backup, and I work closely with him.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Umapathy S
Honored Contributor

Re: Best Practices & Integrety Constraints

Scott,
Having the integrity constraints built inside the db has its own advantages and disadv. Its very easy to write applications and check for error conditions when they are built into db.

But we dont use any integrity constraints and all the applications should take care of themselves. This give flexibility in moving data in and out without much fuss.

Moreover, over a period of time, the applications change and new ones get added. It will be more flexible if dont have those constraints.
my 2 cents.
HTH,
Umapathy
Arise Awake and Stop NOT till the goal is Reached!
Brian Crabtree
Honored Contributor

Re: Best Practices & Integrety Constraints

Personally, I like integrity constraints, as they enforce the fact that data has to fit in a specific way (no duplicate records, parent-child relationships correct, etc). The other nice thing are cascade delete relationships which allow you to remove a number of child records at the same time.

Either way though, they need to be planned out. It will save you a number of headaches with data integrity errors having them though.

Thanks,

Brian
Indira Aramandla
Honored Contributor
Solution

Re: Best Practices & Integrety Constraints

Hi,

Integrity Constraints are the first in once choice to enforce refrential Integrity. Enforcing rules with integrity constraints is more reliable than enforcing the equivalent rules by issuing SQL statements in your application. The semantics of integrity constraints are very clearly defined, so the internal operations that Oracle performs to enforce them are optimized beneath the level of SQL statements in Oracle. Because your applications use SQL, they cannot achieve this level of optimization.
You can define integrity constraints to enforce business rules on data in your tables. Once an integrity constraint is enabled, all data in the table must conform to the rule that it specifies. If you subsequently issue a SQL statement that modifies data in the table, then Oracle ensures that the resulting data satisfies the integrity constraint. Without integrity constraints, such business rules must be enforced programmatically by your application.
In some cases, you might want to enforce business rules through your application as well as through integrity constraints. Enforcing a business rule in your application might provide faster feedback to the user than an integrity constraint.
Because integrity constraints are enforced only when a SQL statement is issued, an integrity constraint can only notify the user of a bad value after the user has entered all 20 values and the application has issued the INSERT statement. However, you can design your application to verify the integrity of each value as it is entered, and notify the user immediately in the event of a bad value.
Some of the ways of definig integruty constraints are :-
1. Creating Indexes for Use with Constraints
2. Using NOT NULL Integrity Constraints

3. Setting Default Column Values
4. Using UNIQUE Key Integrity Constraints
5. Using Referential Integrity Constraints, Nulls and Foreign Keys
6. Deferring Constraint Checks


When a constraint is deferred, the database will check that the constraint is satisfied only at commit time. This is useful in the case where an update to a foreign key will violate the constraint. This will allow you to change the foreign key and then cascade the changes to the parent table before committing the change.

A constraint can also be created "INITIALLY IMMEDIATE DEFERRABLE". This means that the constraint will be checked at "initially" instead of at "commit" time, unless you manually set the constraint to "deferred".

Please refer to the attached document for more info on Integrity Constraints
Never give up, Keep Trying
Graham Cameron_1
Honored Contributor

Re: Best Practices & Integrety Constraints

As a DBA I constantly fight with my developers because I want things to be well designed and with integrity in the database, and they want carte blanche to do what the hell they like with the data.

Leaving the application to enforce integrity constraints is absolutely not best practice. No matter how good the developer, he will move on, and if the relationships are not in the database then how will anyone else pick them up ?!
Oracle looks after all this, on the server, for free.

There is an excellent thread on this at
http://asktom.oracle.com/pls/ask/f?p=4950:8:4132155213046367061::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:6091237036109,

(if that URL doesn't work, to to http://asktom.oracle.com and search for "integrity constraints" - it will be the first hit listed).

For those without time to read it all, the killer quote is
"Not enforcing integrity in the database is like not putting on your seatbelts because that makes the rescue workers job faster when they are cutting you out of the car"

Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.