Posts tagged integrity
How Oracle Database uses internal locks to make statement level constraint validation robust at the transaction level0
Data Constraints are essential in protecting the integrity of the data in any relational database. The Oracle Database provides four types of declarative constraints that help implement various types of data rules. These are: Primary Key, Unique Key, Foreign Key and Check Constraint. Although these constraints can be configured to be enforced at transaction time (when the transaction is committed) by setting them to be deferred, the default behavior of the constraints is to enforce the integrity rule at statement level. That means that when a transaction performs multiple DML statements, the constraints are validated during execution of the statement. When the statement is done, the integrity is ensured (otherwise the statement would have failed) and additional statements can be executed.
In the multi session environment that is the Oracle Database, you could be wondering – as was I – how the Oracle Database ensures that other sessions executing DML operations can not undermine the integrity of the data touched by the current transaction. And how it can do so without needlessly preventing other sessions to perform data manipulation using various forms of locking.
It turns out that More >
In a previous article, I have introduced RuleGen 3.0 – a 2nd generation business rule implementation framework for the Oracle Database: http://technology.amis.nl/blog/?p=12807. RuleGen provides a solid, structured, scalable framework for database developers to implement data constraints in. This article demonstrates how a moderately complex rule is analyzed and designed and implemented with RuleGen 3.0. If you want to try out RuleGen yourself, you can download the framework under trial conditions from http://www.rulegen.com/free-download .
This article illustrates the essential steps in the business rule design and build process:
- describe the business in human readable format – preferably using names of entities and attributes or even better tables and columns
- analyze the events that potentially violate the business rule and should therefore trigger evaluation of the rule
- define the filter condition (a SQL expression) that identifies the records that should be checked for each triggering event (typically only a very small subset of the records in a table need to be explicitly validated upon an event)
- define the rule validation logic in terms of SQL – write the SQL query More >