Constraints are the special elements of SQL which are used to enforce the standards defined by the organization. The definition of a table may include the specification of integrity constraints. There are two types of constraints:

Column Constraints: - These are associated with a single column. However, any column constraint can also be formulated as a table constraint.

Table Constraints: - These are associated with more than one column.

Further Constraints can be divided as: -

1. Integrity Constraints 2. Check Constraints 3. Foreign Key Constraints

The specification of a constraint has the following form:

[<constraint name>] primary key | unique | not null

(A constraint can be named. It is advisable to name a constraint in order to get more meaningful information when this constraint is violated due to, e.g., an insertion of a tuple that violates the constraint. The information will be provided with the name of the constraints so that it is easier to find the violation.)

Integrity Constraints: - We have three types of integrity constraints: not null constraints, primary keys, and unique constraints. Probably the most important type of integrity constraints in a database is primary key constraints. A primary key constraint enables a unique identification of each tuple in a table. Based on a primary key, the database system ensures that no duplicates appear in a table.

Example:

Check Constraints: - Often columns in a table must have values that are within a certain range or that satisfy certain conditions. Check constraints allow users to restrict possible attribute values for a column to admissible ones. They can be specified as column constraints or table constraints. The syntax for a check constraint is

[<constraint name>] check(<condition>)

If a check constraint is specified as a column constraint, the condition can only refer that column.

Example:

If a check constraint is specified as a table constraint, condition can refer to all columns of the table. It is not allowed to refer to columns of other tables or to formulate queries as check conditions. Furthermore, the functions sysdate and user cannot be used in a condition. In principle, thus only simple attribute comparisons and logical connectives such as and, or, and not are allowed. A check condition, however, can include a not null constraint Example: The database system automatically checks the specified conditions each time a database modification is performed on this relation.

Foreign Key Constraints: - A foreign key constraint (or referential integrity constraint) can be specified as a column constraint or as a table constraint:

[<constraint name>] [foreign key (<column(s)>)] references <table>[(<column(s)>)] [on delete cascade]

This constraint specifies a column or a list of columns as a foreign key of the referencing table. The referencing table is called the child-table, and the referenced table is called the parent-table. In other words, one cannot define a referential integrity constraint that refers to a table R before that table R has been created. The clause foreign key has to be used in addition to the clause references if the foreign key includes more than one column. In this case, the constraint has to be specified as a table constraint. The clause references defines which columns of the parent-table are referenced. If only the name of the parent-table is given, the list of attributes that build the primary key of that table is assumed.

Example:

In order to satisfy a foreign key constraint, each row in the child-table has to satisfy one of the following two conditions: " The attribute value (list of attribute values) of the foreign key must appear as a primary key value in the parent-table, or " The attribute value of the foreign key is null (in case of a composite foreign key, at least one attribute value of the foreign key is null)

Example:

Disabling and Enabling Constraints: - If a constraint is defined within the create table command or added using the alter table command the constraint is automatically enabled. A constraint can be disabled using the command

alter table <table> disable <constraint name> | primary key | unique[<column(s)>] [cascade];

To disable a primary key, one must disable all foreign key constraints that depend on this primary key. The clause cascade automatically disables foreign key constraints that depend on the (disabled) primary key.

Example:

In order to enable an integrity constraint, the clause enable is used instead of disable. A constraint can only be enabled successfully if no tuple in the table violates the constraint. Otherwise an error message is displayed. Note that for enabling/disabling an integrity constraint it is important that you have named the constraints.