Re: data integrity constraints: objects vs database

Why would Oracle invest heavily in the development of constraints and tools which implement them, such as Designer, if constraints were unworkable in production? The applications you refer to were designed and developed before constraints were implemented in the database - maybe that's why they don't have them. The same argument would apply to any feature which has been added since, such as objects, where retrofitting was considered too expensive. I imagine retrofitting constraints to a database where the application maintains its own integrity would be extremely problematic and is in any case unnecessary.

Having constraints in the database ensures the integrity of the data independently of apps. accessing it, eliminating the need for multiple apps. to duplicate code for maintaining integrity. It permits the rapid development of lightweight applications by developers who may have skills in front-end languages but not Oracle, in my experience a more and more common situation. It documents the structure of the database from within, allowing it, amongst other things, to be easily accessed by developers and reverse engineered for documentation by case tools.

Of course constraints generate overhead. How wouldn't they since they need to validate that inserted/updated and existing data together comply with constraints? But don;t you think that apps. code that maintains integrity (even if written expertly) will generate at least as much overhead as the database, not least because it is executed remotely? If it doesn't then it suggests that the application may not be checking everything it needs to to preserve integrity. Of course from a DBA point of view if you insist the application does all the work then you a.) Don't have to do the work youself b.) can blame the application later when it doesn't perform as expected. Easy money.

With regard to imports and mass loads, constraints can be disabled and re-enabled, or created as deferrable. To do this for a named table or even a schema to facilitate import requires a few lines of SQL or PL/SQL, not lots of work.

--
Padderz
SYSOP, RevealNet PL/SQL Pipeline
Senior Oracle Developer, Cellular Operations UK

Posted via dBforums
http://dbforums.com