- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Best Practices & Integrety Constraints
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-24-2003 02:45 AM
тАО09-24-2003 02:45 AM
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-24-2003 02:47 AM
тАО09-24-2003 02:47 AM
Re: Best Practices & Integrety Constraints
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
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-24-2003 02:57 AM
тАО09-24-2003 02:57 AM
Re: Best Practices & Integrety Constraints
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-24-2003 09:28 AM
тАО09-24-2003 09:28 AM
Re: Best Practices & Integrety Constraints
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-24-2003 01:07 PM
тАО09-24-2003 01:07 PM
SolutionIntegrity 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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-24-2003 07:29 PM
тАО09-24-2003 07:29 PM
Re: Best Practices & Integrety Constraints
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"