Integrity Constraints

Integrity Constraints

Database integrity means correctness or accuracy of data in the database. A database may have number of integrity constraints. For example -

(i) The Employee ID and Department ID must consists of two digits.

(ii) Every Employee ID must start with letter.

The integrity constraints are classified based on the concept of primary key and foreign key. Let us discuss the classification of constraints based on primary key and foreign key as follows-

Entity Integrity Rule

This rule states that "In the relations, the value of attribute of primary key can not be null".

The NULL represents a value for an attribute that is currently unknown or is not applicable for this tuple. The Nulls are always to deal with incomplete or exceptional data.

The primary key value helps in uniquely identifying every row in the table. Thus if the users of the database want to retrieve any row from the table or perform any action on that table, they must know the value of the key for that row. Hence it is necessary that the primary key should not have the NULL value.

Referential Integrity Rule

• Referential integrity refers to the accuracy and consistency of data within a relationship.

• In relationships, data is linked between two or more tables. This is achieved by having the foreign key (in the associated table) reference a primary key value (in the primary or parent - table). Because of this, we need to ensure that data on both sides of the relationship remain intact.

• The referential integrity rule states that "whenever a foreign key value is used it must reference a valid, existing primary key in the parent table".

• Example:Consider the situation where you have two tables Employees and Managers. The Employees table has a foreign key attribute entitled Managed By, which points to the record for each employee's manager in the Managers table.

Referential integrity enforces the following three rules:

i) You cannot add a record to the Employees table unless the Managed By attribute points to a valid record in the Managers table. Referential integrity prevents the insertion of incorrect details into a table. Any operation that doesn't satisfy referential integrity rule fails.

ii) If the primary key for a record in the Managers table changes, all corresponding records in the Employees table are modified.

iii) If a record in the Managers table is deleted, all corresponding records in the Employees table are deleted.

Advantages of Referential Integrity

Referential integrity offers following advantages:

i) Prevents the entry of duplicate data.

ii) Prevents one table from pointing to a nonexistent field in another table.

ii) Guaranteed consistency between "partnered" tables.

iii) Prevents the deletion of a record that contains a value referred to by a foreign key in olds another table.

iv) Prevents the addition of a record to a table that contains a foreign key unless there is etail a primary key in the linked table.

Review Question

1. Discuss the entity Integrity and referential integrity constraints. Why are they important? Explain them with suitable examples.