Integrity Constraints Management.

A wise man once said: "Data must protect itself!". This is exactly what database constraints do. A rule that restricts the values in a database to maintain the valid set of data. Constraints are rules that restricts the values for one or more columns in a table. Constraints are created during table creations or later after the objects are created. Before you begin to start the article, I recommend you to go over "DATABASE CONSTRAINTS" if you haven't already. This talks in detail about the Different Database Constraints types.

Constraint management is very crucial while loading data or building a database. Constraint can be DISABLED or ENABLED. When Constraint is enabled, data is checked as they are inserted/updated. IF a constraint is disabled, then data that does not conform can be allowed to enter the database. Besides plain Vanilla ENABLE and DISABLE constraint there are few more Integrity Constraint's states.

1. ENABLE VALIDATE
2. DISABLE VALIADATE
3. ENABLE NOVALIADATE
4. DISABLE NOVALIADATE

ENABLE VALIDATE: This constraints state ensures that existing data conforms to the constraints and any future data.

ENABLE NOVALIDATE: During this state of constraint, all subsequent statements are checks for conformity to the constraints. However any existing data in the table is not checked. A table with enable no validate constraints can contain invalid data. It is not possible to add new invalid data. This state is the most useful in data warehouse. Also useful if we know there’s data that violates the constraint but we want to quickly put on a constraint to prevent further violations, with the intention to clean up any possible violations at some future point in time.

DISABLE VALIDATE & DISABLE NOVALIDATE are both self explanatory that disables VALIDATE/NOVALIDATE.

SYNTAX:

ALTER TABLE table_name
    DISABLE CONSTRAINT constraint_name;
 
ALTER TABLE table_name
    ENABLE CONSTRAINT constraint_name; 
   
ALTER TABLE table_name
 ENABLE NOVALIDATE CONSTRAINT constraint_name;
 
ALTER TABLE table_name
 ENABLE VALIDATE CONSTRAINT constraint_name;

Efficient Use of Integrity Constraints

1. Disable state
2. Perform the operation (load, export, import).
3. Enable novalidate state.
4. Enable state.

Some benefits of Using Constraints in this order are:

1. No locks are held.
2. All constraints can go to enable state concurrently
3. Constraint enabling is done in parallel.
4. Concurrent activity on table is permitted.

Constraints are powerful to database, use them right to protect data itself and make your database healthy an wise.