Types of Constraint States used in ETL

Disable Constraint:  When the constraint is in the disable state, although the constraint continues to reside in the data dictionary, all the data get entered into the database without being checked by the constraint.

Enable Constraint:  When the constraint is in the enable state, data is not entered into the database unless it fulfills the condition of the constraint.  This is the default state of the constraint.

Validate Constraint:  When the constraint is in the validate state, the constraint ensures that the data entered conforms to the constraint specifications.

Novalidate Constraint:  When the constraint is in the novalidate state, data entered hereafter will be checked to conform to the constraint specifications.  This means that data entered earlier, which did not conform to the constraint might exist in the table.

Combination of the constraint states:

• Enable, disable, validate and novalidate are independent properties of the constraints and various combinations of the above can be crated according to the requirement.
• When the constraint is enable validate, it is the same as enable and all data present in the table must conform to the constraint specifications.
• When the constraint is enable novalidate, existing rows may violate the constraint but any new rows inserted or modified must conform to the constraint specifications 
• When the constraint is disable validate, it ensures that all the data in the table conforms to the constraint specifications.  [While enabling the constraint after a time period of disable, the data entered during the disable period will be validated or checked]. 
• When the constraint is disable novalidate, it is the same as disabled and the data entered is not ruled by the constraint specifications.  This state is useful when large amount of data has to be loaded which has already been validated and time is saved, as it need not be revalidated.

See the following situation,

The max_level column of the itemfile table is having the check constraint as check(max_level<500).  Now we drop the check constraint using the drop command as follows.

Alter Table Itemfile Drop Constraint Max; 

Subsequently, we alter the table and apply the following check constraint.

Alter Table Itemfile Add Constraint Max1 Check(Max_Level<400);

Now oracle will give an error check constraint violated.  This is because the table has data in that column following the earlier constraint set.  If we apply the new constraint as follows the validations will only be for the future entries after the new constraint application.  The command is,

Alter Table Itemfile Add Constraint Max1 Check(Max_Level<400) Novalidate;

The items already existing with the max_level between 400 and 500 will remain as it is.  Only fresh data inserted will be validated to conform to the constraint specifications.