Data Integrity - Constraints and Triggers

All data stored in a database must adhere to certain business rules.
For example, there may be a business rule specifying a minimum 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 - Allow existing data to not 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 for Inserts and Updates but will not validate any data that already exists 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 Unique 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 converting a NOVALIDATE constraint to VALIDATE, all data must be checked, this may take a long time depending on the amount of data to be validated.

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:
ALTER SESSION SET CONSTRAINTS DEFERRED
ALTER SESSION SET CONSTRAINTS IMMEDIATE
Type 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. These ALTER SESSION SET CONSTRAINTS… statements will not work for any constraints that are created as NOT DEFERRABLE (the default).
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