4 Basic Integrity Constraints for a Database Management System
First of all we’ll get a clear idea about what these integrity constraints are. Integrity constraints are a set of rules associated when performing data inserting, deleting, updating for a database. For an example, if we consider about a school, the set of rules that are to be followed by us in the school is our code of conduct. In the similar way Integrity constraints are the the set of rules that have to be followed when performing certain operations to a database.
These Integrity Constraints also known as ICs in shorter format are basically of 4 types. Namely, Domain Constraint, Entity Integrity Constraint, Referential Integrity Constraint and Key Constraint. Now we’ll taka a look at theses constraints one by one to get a clear idea about what these are.
Domain Constraint
After mapping our conceptual database design into the logical database design we get a set of relations(tables). So in these relations we start entering the data that is required for the database. In these relations for each attribute there is a column allocated. When entering data to these columns we have to know what type of data we are going to insert. There are few data types such as integer, string, date, time etc. Inserting the correct type of data into the correct field is the first rule which is known as Domain Constraint. Refer the example below.
In the above example the primary key of the table is “StdId”. A primary key is used to identify a record uniquely. Its data type is String. When we consider the other attributes “Sname”, “SAddress” and “Sage” attributes, they are of String, String and integer data type respectively. But as you can see in the StdId IT1985 row the “Sage” value is given as a string value which is “seventeen”. So its is an example where the domain constraint is violated. E.g.- A good real life example for this particular constraint is pumping Petrol into a vehicle which is running from Diesel. Since the vehicle is running from Diesel it should always get diesel because that vehicle is designed to run with diesel. So inserting petrol into such vehicles will cause vehicle breakdowns
Entity Integrity Constraint
When inserting data into relations, we identify a tuple(row) using the primary key which is unique for each and every tuple in the relation. So the Entity Integrity Constraints means that the Primary Key value should not be a NULL value at any given time. Refer the example below.
In the same Student table if we enter nothing for the “StdID” field which is the primary key of the relation, that is a point where the Entity Integrity Constraint is violated. So that is not allowed when a database is being created. E.g.- Its something like you have a vehicle but you dont have a driver to drive that vehicle. So without a driver a vehicle can’t be used for its maximum potential.(Not auto-pilot)
Referential Integrity Constraint
This Integrity constraint is dealing with 2 tables. Here, the foreign key value of a table should match with an existing value in the referenced tables’ primary key values. Also the data types of the foreign key and the primary key also should be the same. To explain it more clearly, let me take the example below.
So in the above example, the table with ProjID(primary key) and PName attributes is the Project table which is the referenced relation of the Student table. In order to find out what project is given to what student the primary key of the project table is taken into the student table as the foreign key. So in the second tuple you can see that there is a ProjID which is not defined in the Project table which is “4”. That is an example for the violation of Referential Integrity constraint because “4” is not defined in the project table. So there you can see that both foreign key of the student table(ProjID) and the primary key of the Project table(ProjID) have the same data type which is integer. But in the Project table there can be projects without students also. In the above example ProjID- 3 doesn't have any students. Also keep in your mind that the names of the foreign key and the primary key should not be the same all the time. E.g.- Suppose that there are ministers without a ministry in the parliament and if they are assigned to a certain ministry without knowing.
Key Constraint
As mentioned earlier in a relation, to identify each entries uniquely, there is a primary key value for each of row. So here Key constraint means that the primary key values of a single relation should not be repeated. Refer the below example.
So here the “StdID- IT2098” is repeated twice in the second and the fourth rows in the Student relation. That is a situation where the Key constraint is violated when entering data into the Student table. E.g- Existence of a vehicle with same registration number is a good example for this particular situation.
So those are the 4 basic Integrity constraints that will be useful when implementing a database.
