Integrity Constraints in DBMS

What are integrity constraints?

Integrity constraints are rules that are to be applied on database columns to ensure the validity of data. Every time data is entered into that particular column, it is evaluated against the constraint and only if the result comes out to be true, then the data is inserted into the column. Thus these constraints help to maintain the integrity of the data. These are applied as a set of rules which ensure data quality requirements, hence the name integrity constraints. The database manages consistency of the same data across different tables through the integrity constraints. Thus integrity constraints are extremely essential as they constantly check that the changes made in the database by any authorized user do not create any inconsistency.

There are various types of integrity constraints that the database provides. Each has its own function and each ensures data integrity in its own way. Integrity constraints can be divided into the following:

Entity integrity constraint

Domain Integrity constraint

Referential Integrity constraint

Entity Integrity Constraint:

Entity integrity constraints as the name implies is applied on each entity. i.e. it is applied on individual rows. The constraint here is to have a unique value for each row in the column or a group of columns it is applied to. This attribute is essential when a particular record or row of data is to be accessed. It can be accessed using the entity integrity constraint by supplying the unique value and accessing the entire record.

There are two such constraints which ensure uniqueness of data. They are-

Primary key
Unique keyword

Primary key ensures that values in a column are unique so that duplicate values are not allowed and also the primary key column cannot be null. Thus it focuses on two properties- uniqueness and not null. A table can contain only one primary key. Primary key can consist of a column or a group of columns. It is used to uniquely identify records in a table.

Unique keyword is just like the primary key but it allows null values. Both these are defined on columns during defining the structure of table. Thus these are used in data definition language.

Domain Integrity Constraints:

These are the constraints on the domain value and thus are column level constraints unlike entity integrity constraint which are row level. The domain integrity constraints are used to impose restrictions on some particular column. Thus they affect the domain value. These can be enforced in the form of the following-

Check value
Default value

Check is used to impose certain checks like checking if a value is greater than or lesser than a particular value etc. thus the upper and the lower limit can be set.

Default value is the value to be provided in case no value is provided by the user. We can set a default value for any column depending on its data-type. These are also used during defining the structure of the tables, in the data definition language statements.

Referential Integrity Constraint:

Referential integrity constraint makes sure that the values in the column on which it is applied are already present in the column it is referring to. Thus here a column of a table refers to the other column of the same or different table. This ensures that the values are consistent and similar in both the columns. This is implemented using-

Foreign key

Foreign key normally references the primary key of same or another table. But it can refer to other columns too. Whenever, same type of attribute exists in two different tables, the attribute in one of the table is declared as the primary key and in the other, it is made foreign key, so that the values in both become consistent. Foreign key is dependent on primary key.

Integrity Constraints in DBMS

What are integrity constraints?

Entity Integrity Constraint:

Domain Integrity Constraints:

Referential Integrity Constraint:

Post Comment:

Subscribe to Email

Online Members