NOT NULL Constraint

The NOT NULL constraint is a column-level constraint that requires that the column must contain a value whenever a row is inserted or updated. The NOT NULL constraint must be defined as part of the column definition. Use a NOT NULL constraint when the data is required for the integrity of the database. For example, if all employees must belong to a specific department, then the column that contains the department identifier should be defined with a NOT NULL constraint. On the other hand, do not define a column as NOT NULL if the data might be unknown or might not exist when rows are added or changed, for example, the second, optional line in a mailing address.

A primary key constraint automatically adds a NOT NULL constraint to the columns included in the primary key, in addition to enforcing uniqueness among the values. 

Check Constraint

A check constraint requires that a column (or combination of columns) satisfies a condition for every row in the table. A check constraint must be a Boolean expression that is evaluated using the column value about to be inserted or updated to the row. Use check constraints when you need to enforce integrity rules based on logical

expressions, such as comparisons. Never use CHECK constraints when any of the other types of integrity constraints can provide the necessary checking. Examples of check constraints include the following:

■ A check constraint on employee salaries so that no salary value is less than 0.

■ A check constraint on department locations so that only the locations Boston, New York, and Dallas are allowed.

■ A check constraint on the salary and commissions columns to prevent the commission from being larger than the salary.