Database: When to Use CHECK Integrity Constraints (Oracle)

I have a table that contains lookups (constant data values in the system that doesn't change by the system when it up and running) data for the system, and this data in handled manually by developers, in database generation script.

I have some values should be in specific range or between certain values, dates not less than today and values inserted in columns based on other column value and so on.

There are two ways to add this business logic, in trigger (general one) or on table as check constraint (specific one).

I used the table check constraint choice, because this business validations is specific to table and is simple.

When to use CHECK constraints:

1. Use CHECK constraints when you need to enforce integrity rules based on logical expressions, such as comparisons.
2. Never use CHECK constraints when any of the other types of integrity constraints can provide the necessary checking (unique, primary, not null constraints).

Examples of CHECK constraints include the following:

1. A CHECK constraint on employee salaries so that no salary value is greater than 10000.
2. A CHECK constraint on department locations so that only the locations "CAIRO", "HURGADA", and "ALEXANDRIA" are allowed.
3. A CHECK constraint on the salary and commissions columns to prevent the commission from being larger than the salary.

Restrictions on CHECK Constraints
A CHECK integrity constraint requires that a condition be true or unknown for every row of the table. If a statement causes the condition to evaluate to false, then the statement is rolled back. The condition of a CHECK constraint has the following limitations:

1. The condition must be a boolean expression that can be evaluated using the values in the row being inserted or updated.
2. The condition cannot contain sub queries or sequences.
3. The condition cannot include the SYSDATE, UID, USER, or USERENV SQL functions.
4. The condition cannot contain the pseudo columns LEVEL, PRIOR, or ROWNUM.
5. The condition cannot contain a user-defined SQL function.

Designing CHECK Constraints
When using CHECK constraints, remember that a CHECK constraint is violated only if the condition evaluates to false; true and unknown values (such as comparisons with nulls) do not violate a check condition. Make sure that any CHECK constraint that you define is specific enough to enforce the rule.

For example, consider the following CHECK constraint:

At first glance, this rule may be interpreted as "do not allow a row in the employee table unless the employee's salary is greater than zero or the employee's commission is greater than or equal to zero." But if a row is inserted with a null salary, that row does not violate the CHECK constraint regardless of whether the commission value is valid, because the entire check condition is evaluated as unknown.

In this case, you can prevent such violations by placing NOT NULL integrity constraints on both the SAL and COMM columns.

Rules
A single column can have multiple CHECK constraints that reference the column in its definition. There is no limit to the number of CHECK constraints that can be defined that reference a column.

The order in which the constraints are evaluated is not defined, so be careful not to rely on the order or to define multiple constraints that conflict with each other.

According to the ANSI/ISO standard, a NOT NULL integrity constraint is an example of a CHECK integrity constraint, where the condition is the following:

Therefore, NOT NULL integrity constraints for a single column can, in practice, be written in two forms: using the NOT NULL constraint or a CHECK constraint. For ease of use, you should always choose to define NOT NULL integrity constraints, instead of CHECK constraints with the IS NOT NULL condition.

In the case where a composite key can allow only all nulls or all values, you must use a CHECK integrity constraint. For example, the following expression of a CHECK integrity constraint allows a key value in the composite key made up of columns C1 and C2 to contain either all nulls or all values:

Defining Integrity Constraints
1- With Create table command:
The following examples of CREATE TABLE statements show the definition of several integrity constraints:

2- With alter table command
You can also define integrity constraints using the constraint clause of the ALTER TABLE command. The syntax for creating a check constraint in an ALTER TABLE statement is:

Disable a Check Constraint:
The syntax for disabling a check constraint is:

Why Disable Constraints?
During day-to-day operations, constraints should always be enabled. In certain situations, temporarily disabling the integrity constraints of a table makes sense for performance reasons. For example:

1. When loading large amounts of data into a table using SQL*Loader.
2. When performing batch operations that make massive changes to a table (such as changing everyone's employee number by adding 1000 to the existing number).
3. When importing or exporting one table at a time.

Note: Turning off integrity constraints temporarily speeds up these operations.

References:

1. Maintaining Data Integrity through Constraints.
2. Oracle Check Constraint tips.
3. Oracle/PLSQL: Check Constraints.