Types of Integrity Constraints in SQL

Types of Integrity Constraints:

In SQL, Integrity constraints are a set of rules. It is used to maintain the quality of information. While creating a table, you can place a certain limitation on the value stored in the table. There are six types of Integrity Constraints in SQL are:

1. NOT NULL: It prevents a column from accepting null values. If you try to insert a null value in such a column, it will be rejected. It doesn’t mean a zero value.

2. UNIQUE: It ensures that values entered into a column are all different. A column with this constraint will not accept any duplicate values.

3. PRIMARY KEY: To declare a column as the primary key of the table, use the PRIMARY KEY constraint. There can be only one primary key in a table.

4. CHECK: This constraint is used to control the values entered into a field. A condition is specified along with the CHECK constraint which must be satisfied by all the values being entered into the column, otherwise, the value will be rejected.

5. DEFAULT: This constraint is used to assign default values to a column before any value is assigned to it.

6. REFERENCES: A foreign key has a value that forms the primary key in another table. The two tables thus get related by using the foreign key. Columns that are chosen as a foreign key should not have values other than that present in the primary key of a related table. This referential integrity is implemented using the REFERENCES constraint. This constraint is followed by the name of the related table and its primary key.

Example:

create table DEPARTMENT
{
DEPT_CODE NUMBER(2) PRIMARY KEY, DEPT_NAME VARCHAR(10) NOT NULL UNIQUE CHECK (DEPT_NAME IN ('ACCOUNT', 'RESEARCH', 'SALES', 'OPERATION'))
};