|
All data stored in a database must adhere to certain business
rules.
For example, there may be a business rule specifying a munimum hourly wage
for any employee or another rule stating that the discount for SALE items cannot
be more than 100%.
In either case if an INSERT or UPDATE statement attempts to violate
the integrity rule, Oracle
must roll back the statement and return an error.
Integrity Constraints
An integrity constraint defines a business rule for
a table column. When enabled, the rule will be enforced by oracle (and so
will always be true.)
To create an integrity constraint all existing
table data must satisfy the constraint.
Default values are also subject to integrity constraint
checking (defaults are included as part
of an INSERT statement before the statement is parsed.)
If the results of an INSERT or UPDATE statement violate
an integrity constraint, the statement will be rolled back.
Integrity constraints are stored as part of
the table definition, (in the data dictionary.)
If multiple applications access the same table they will all adhere to the
same rule.
The following integrity constraints are supported by Oracle:
NOT NULL
UNIQUE
CHECK constraints for complex integrity rules
PRIMARY KEY
FOREIGN KEY integrity constraints -
referential integrity actions: – On Update – On Delete
– Delete
CASCADE – Delete SET NULL
Constraint States
The current status of an integrity constraint can be changed to any of the
following 4 options using the CREATE TABLE or ALTER
TABLE statement.
- ENABLE ensure that all incoming data conforms to the constraint
- DISABLE allow incoming data, regardless of whether it conforms to the constraint
- VALIDATE ensure that existing data conforms to the constraint
- NOVALIDATE existing data does not have to conform to the constraint
These can be used in combination
ENABLE { VALIDATE | NOVALIDATE }
DISABLE { VALIDATE | NOVALIDATE }
- ENABLE VALIDATE is the same as ENABLE.
- ENABLE NOVALIDATE means that the constraint is checked, but it does not
have to be true for all rows. this will resume constraint checking
on disabled constraints without first validating all data in the table.
- DISABLE NOVALIDATE is the same as DISABLE.
- DISABLE VALIDATE disables the constraint, drops the index on the constraint,
and disallows any modification of the constrained columns.
for a UNIQUE constraint, this enables you to load data
from a nonpartitioned table into a partitioned table using the
ALTER TABLE.. EXCHANGE PARTITION clause.
Indexes to support constraints:
When a unique or primary key is Enabled,
if there is no existing index, a unique index is automatically created. When
a unique or primary key is Disabled, the unique index is dropped.
When a constraint is Validated,
all data must be checked. (this can be very slow.)
ENABLE
VALIDATE does not block reads, writes, or other DDL statements. It
can be done in parallel.
Effect of constraint failure
Any SQL INSERT, UPDATE or DELETE command applied to a table with constraints enabled has the possibility of failing.
For example updates applied to a Parent Table may fail if the statement leaves orphaned rows in a child table, INSERTs against a Child Table may fail if a matching foreign key value does not exist in the parent table.
Constraint failures will result in the statement being rolled back - coding an application front end to deal with such errors is generally easier than handling all the business rules in code. You can design applications to use constraint data dictionary information to provide user feedback about integrity constraint violations.
Deferring a constraint
You can defer checking constraints for validity until the end of the transaction, so the constraint rules don't have to be met until the whole transaction is committed.
This can be defined for each constraint with
keywords in the CONSTRAINT clause:
DEFERRABLE or NOT DEFERRABLE
INITIALLY DEFERRED or INITIALLY IMMEDIATE
This can be defined for each transaction with the SET CONSTRAINTS statement:
SET CONSTRAINTS DEFERRED
SET CONSTRAINTS IMMEDIATE
You can use
SET CONSTRAINTS for a list of constraint names or for ALL
constraints.
The ALTER SESSION statement also has clauses to SET CONSTRAINTS IMMEDIATE or DEFERRED.
The SET CONSTRAINTS statement is disallowed inside of triggers.
Pros and cons
Constraints are preferable to application code, database triggers or stored procedures. Because a constraint is defined once for each table (in the data dictionary) changes to business rules can be applied in one place.
The Oracle query optimizer can utilise integrity constraint declarations.
Constraints do cause a small loss in performance. The cost of an integrity constraint is, at most, the same as executing an equivalent SQL statement.
A UNIQUE key constraint will not prevent the input of multiple nulls, because
a null is not considered equal to anything. (NULL <> NULL)
You can prevent NULLs being added to a column by adding a NOT NULL constraint.
Columns with both UNIQUE keys and NOT NULL integrity constraints are common.
Database Triggers
A database trigger is a procedure written in PL/SQL,
Java, or C that will run implicitly when data is modified
or when some
user or
system actions occur.
Triggers can be used in many ways e.g. to enforce complex integrity constraints or to audit data modifications. Triggers should not be used to enforce business rules or referential integrity rules that could be implemented with simple constraints.
Triggers are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used.
A row trigger is fired once for each row affected by an UPDATE statement.
A statement trigger is fired once, regardless of the number of rows in the table.
BEFORE triggers execute the trigger action before the triggering statement
is executed. This type of trigger is commonly used if the trigger will derive
specific column values or if the trigger action will determine whether the
triggering statement should be allowed to complete.
Appropriate use of a BEFORE trigger can eliminate unnecessary processing of
the triggering statement.
AFTER triggers execute the trigger action after the triggering statement is executed.
For any given table you can have multiple triggers of the same type for the
same statement.
E.g. multiple AFTER UPDATE triggers on the same table
{BEFORE|AFTER} {row| statement} Trigger
Some Views cannot be directly modified through DML statements (INSERT, UPDATE,DELETE). INSTEAD OF triggers allow you to work around this limitation by firing the trigger instead of executing the DML statement.
Triggers on system events can be defined at the database level or schema level.
STARTUP, SHUTDOWN, SERVERERROR, User LOGON and LOGOFF,
Modifications to schema objects: {BEFORE | AFTER} {CREATE|ALTER|DROP}
Data Integrity
A database
trigger is not the same as an integrity constraint. A
database trigger defined to enforce an integrity rule does not check data
already loaded into a table. Therefore, it is recommended that you
use a trigger only when the integrity rule cannot be enforced by
an integrity constraint.
ORA-00001 - Unique constraint violated. (Invalid data has been rejected)
"Love withers under constraint; its very
essence is liberty; it is
compatible neither with obedience, jealousy, nor fear: it is there most
pure, perfect and unlimited, where its votaries live in confidence,
equality and reserve." - Shelley
See also
Oracle Supplied Packages
PL/SQL commands