Integrity Constraints in DBMS

Constraints ensure that when authorized users modify the database, the data consistency is not disturbed. When designing a database schema, integrity constraints are introduced. In SQL DDL commands such as 'create table' and 'alter table', constraints are specified.

The general form of 'create table' and 'alter table' commands specifying the integrity constraint is:

create table r (A1 D1, 2 D2, ……An Dn, (integrity constraint1), …… (integrity constraintn));

alter table r add integrity constraint;

Each time a database update occurs, the system checks the specified constraint. Updates that modify the database but violate an integrity constraint are rejected. When a database is updated, it is the authorization and integrity manager who tests the integrity constraint.

It is possible to specify the integrity constraint as an arbitrary predicate. However, testing arbitrary predicates can result in overhead. In order to minimize testing overhead, the database system has implemented some integrity constraints.

Types of Integrity Constraints in DBMS

• Entity Integrity Constraints
• Referential Integrity Constraints
• Domain Constraints
• Assertion

1. Entity Integrity Constraints

As a result of an entity integrity constraint, a relation's primary key attribute cannot accept a null value. The primary key attribute value uniquely identifies an entity in a relation. As a result, it cannot be null.

(Student_id varchar (5) , name varchar (20) not null, depart_name varchar (20),

primary key (Student_id));

When an attribute is declared as a primary key, it is not necessary to explicitly specify that it is not null. Additionally to primary key constraints, entity integrity constraints include unique key constraints.

create table r (A1 D1, 2 D2, ……An Dn, unique (Ak1 , Ak2, . . . , Akm) ));

Candidate keys consist of the set of attributes listed as unique. In a relation, no two entities (tuples) must have the same candidate key attribute value. Candidate key attributes can accept ‘null’ values unless they are specifically declared to be ‘not null’.

2. Referential Integrity Constraints

In order for the values of a set of attributes in one relationship to appear the same in another, they must also appear the same in both relations.

In order to understand this clearly, let's look at an example of student relations:

Student(Student_id, name, depart_name)

Student relation has the attribute depart_name, as we can see here. Consider a student tuple whose depart_name is Chemistry. There will also be a tuple in the Department relation with the depart_name attribute set to Chemistry. How did the student pick the Chemistry department if the Department relation didn't match the department_name Chemistry?

A student relation has an attribute that references a department relation attribute. Foreign keys are attributes or sets of attributes in a relation that refer to another relation in the database.

Foreign key attributes are guaranteed to occur as primary key attributes of the referenced relation. The primary attribute of the department relationship is depat_name, just like the foreign key attribute above.

In the relation, the referential integrity constraint is defined as follows:

Student table should be created

(Student_id varchar (5) , name varchar (20) not null, depart_name varchar (20),

primary key (Student_id),

foreign key(depart_name) references Department);

When a database constraint governing referential integrity is violated, an action that violates referential integrity is rejected. The depart_name attribute of some Department relation tuple does not exist in the depart_name attribute of a student tuple inserted in the student relation. So, the request is rejected.

3. Domain Constraints

Domain constraints ensure that an attribute's value matches its domain. Whenever we declare a relation to a database and its attribute, we specify a specific domain.

A domain defines all possible values that an attribute can have. By defining the domain of an attribute, we specify the possible values for that attribute.

(Student_id varchar (5) , name varchar (20) not null, depart_name varchar (20),

primary key (Student_id),

(Foreign key(depart_name) refers to Department);

In the above example, the name attribute of the student relation can only accept string values of variable length, it cannot accept integer values, dates, or times.

There are three constraints that can be studied under domain constraints.

1. Null constraint not allowed
2. default constraint
3. check clause constraint

Not Null Constraint:

In SQL, null is considered to be the legal value for all domains. Therefore, null values are acceptable for attributes. However, there are some attributes that cannot be null.

Consider a student tuple with a null value in the 'name' attribute. It stores information about an unknown student. We must specify a not null constraint in these cases for a specific attribute in the relation.

When an attribute is specified as not null, we restrict its domain to not accept null values. Let's see how we specify not null attributes.

create table Student

(Student_id varchar (5) , name varchar (20) not null, depart_name varchar (20));

In SQL, particularly the primary key attribute has a not null constraint on it. The attribute in relation you declare as a primary key need not be specifically declared as not null.

Default Value Constraint:

By using default value constraint, you can set a default value for an attribute. In the case of a default constraint, if no value is specified for an attribute, that attribute will hold the default value.

For example:

create table instructor

(instructor_id varchar (5),

name varchar (20) not null,

depart_name varchar (5),

salary numeric (8,2) default 0);

During the insertion of a tuple in an instructor relation, if the salary attribute is not provided with a value, then its value is set to 0.

Clause for checking:

When a new tuple is inserted into a relation, the check clause constraint ensures it must satisfy the predicate specified in the check clause. Let's look at an example:

create table Student

(Student_id varchar (5) , name varchar (20) not null, depart_name varchar (20),

primary key (Student_id),

check (depart_name in(‘Comp.Sci.’, ‘Elec.Eng.’, ‘Physics’, ‘Biology’)));

According to the SQL standard, the predicate that is placed inside the check clause can be a subquery. But, today’s widely used database products do not allow the predicate of check clause to contain a subquery.

4. Assertion

Assertions are predicates that include a condition that must always be satisfied by the database. The entity integrity constraints, domain integrity constraints, and referential integrity constraints are actually special forms of assertion.

Nevertheless, constraint cannot always be expressed in these special forms. This is why predicates have to be used. The assertion is validated before any modification to the database, and if it fails to satisfy the constraint, the modification is rejected.

We discussed integrity constraints in SQL in this article. Integrity constraints ensure data consistency in databases.