Integrity constraints ensure that when the authorized users modify the database they do not disturb the data consistency. Integrity constraints are introduced while designing the database schema. The constraints are specified within the SQL DDL command like ‘create table’ and ‘alter table’ command.
Let’s see, the general form of ‘create table’ and ‘alter table’ command specifying the integrity constraint:
create table r (A1 D1, 2 D2, ……An Dn, (integrity constraint1), …… (integrity constraintn));
alter table r add integrity constraint;
The database system checks the specified constraint each time the database is updated. Any updation that causes modification to the database but, violates an integrity constraint, are rejected. It is the authorization and integrity manager who test the integrity constraint each time a database is updated.
Though the integrity constraint can be specified as an arbitrary predicate. But, testing arbitrary predicates can cause overheads. So, the database system has implemented certain integrity constraints to minimize the testing overhead.
Types of Integrity Constraints in DBMS
1. Entity Integrity Constraints
Entity integrity constraint ensures that the primary key attribute in a relation, should not accept a null value. This is because the primary key attribute value uniquely defines an entity in a relation. So, it being null would not work.
create table Student
(Student_id varchar (5) , name varchar (20) not null, depart_name varchar (20),
primary key (Student_id));
Whenever we declare any attribute in relation as the primary key attribute then it not necessary to specify it explicitly to be not null. In addition to primary key constraint, entity integrity constraint includes unique key constraint.
create table r (A1 D1, 2 D2, ……An Dn, unique (Ak1 , Ak2, . . . , Akm) ));
The set of attributes listed as unique forms the candidate key. No two entities (tuples) in a relation must have equal values for candidate key attribute. Candidate key attributes can accept ‘null’ values unless they are specifically declared to be ‘not null’.
2. Referential Integrity Constraints
Referential integrity ensures that the values for a set of attributes in one relation must also appear the same for the particular set attributes in another relation.
To understand this clearly let us take an example of student relation:
Student(Student_id, name, depart_name)
Here, we can see that student relation has the attribute depart_name. Now suppose, we have a student tuple with the depart_name value as Chemistry. Then the Department relation must also have a tuple that would have depart_name attribute value as Chemistry. If the Department relation does not have any tuple with the depart_name Chemistry how had the student opt the chemistry department?
The student relation has an attribute that refers to an attribute of the Department relation. So, the attribute or set of attributes in a relation referring to another relation in the database is called foreign key.
Foreign key attribute or set of attributes is guaranteed to occur as a primary key attribute of the referenced relation. Like above the foreign key attribute, depat_name in student relation is the primary attribute of department relation.
Let us see how the referential integrity constraint is defined in the relation
create table Student
(Student_id varchar (5) , name varchar (20) not null, depart_name varchar (20),
primary key (Student_id),
foreign key(depart_name) references Department);
when the referential integrity constraint for a database is violated then the action causing the violation of referential integrity is rejected. It means if you insert a student tuple in the student relation with the depart_name value that doesn’t exist in the depart_name attribute of some tuple in Department relation. Then this action would be rejected.
3. Domain Constraints
Domain constraint ensures that the value associated with an attribute is justifying its domain. Whenever we declare any relation to the database while declaring its attribute we specify a particular domain for each attribute.
The domain of an attribute specifies all the possible values that attribute can hold. Declaring the domain of an attribute it acts as a constraint on that attribute which specifies the possible values that it can take.
create table Student
(Student_id varchar (5) , name varchar (20) not null, depart_name varchar (20),
primary key (Student_id),
foreign key(depart_name) references Department);
Like in the above example the name attribute of student relation can only accept string value of variable length, it cannot accept an integer value or a date or time value.
Now, there are three constraints which we can study under domain constraint.
- not null constraint
- default constraint
- check clause constraint
Not Null Constraint:
Null is considered to be the legal value for all the domain specified in SQL. So, it’s ok for an attribute to have null value. But, there are some attributes which need not be null.
Consider a student tuple that has a null value in it’s ‘name’ attribute. In this case, we are storing information about an unknown student. So, in these cases, we have to particularly specify not null constraint for the specific attribute in a relation.
By specifying an attribute to be not null we restrict the domain of that attribute for not accepting the null values. Let us see how do we specify an attribute to be not null.
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:
Using default value constraint, you are able to set a default value for an attribute. In case if you don’t specify any value for an attribute on which default constraint is specified it hold the specified 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);
This command specifies that while inserting a tuple in instructor relation if no value is provided for the salary attribute then its value is set to be 0.
Check Clause:
The check clause constraint ensures that when a new tuple is inserted in relation it must satisfy the predicate specified in the check clause. Let’s see an example of the check clause:
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
An assertion is a predicate that contains a condition that must always be satisfied by the database. Actually the entity integrity constraints, domain integrity constraints, referential integrity constraints are the special form of assertion.
But, every time it is not possible to express constraint in these special forms only. So, we have to express them in predicates. Before any modification to the database, the assertion is validated and if does not satisfy the constraint the modification is rejected.
So, this was all about integrity constraints in SQL. They are important to maintain the consistency of data in the database.
Leave a Reply