Integrity Constraints – Overview

This post is intended to introduce integrity constraints, their need and their usage in SQL.

Integrity constraints are pre-defined sets of guidelines used in Database Management Systems to apply to table fields (columns) or relations in order to maintain the general validity, integrity, and consistency of the data contained in the database table.

The guidelines that the data columns in a table must adhere to are called integrity constraints. These are employed to limit the kinds of data that may be inserted into a table. This indicates that the database’s data is accurate and trustworthy. Integrity constraints can be used at the column or table level. While the column level requirements only apply to one column, the table-level integrity constraints apply to the entire table.

This means, that every time a table insert, update, delete, or alter action is carried out, all the conditions or rules specified in the integrity constraint are evaluated. Only if the constraint’s outcome is True can the data be added, modified, deleted, or otherwise changed. Integrity constraints are helpful in preventing any unintentional database harm by a legitimate user.

These are classified into 4 types.

1. Domain Integrity: Domain integrity, which assures that every data in a field contains legitimate values, is established by the permitted values of an attribute. Data types such as decimal, character, and integer as well as the maximum length for data are what make up domain integrity.
2. Entity Integrity Constraint: The goal of entity integrity is to make sure that each row of a table has a primary key value that is both distinct and non-null, which is the same as saying that each row in a table represents a single instance of the entity type that the table is modelling.
3. Referential Integrity: The relationship between tables is referred to as referential integrity. A primary key is required for each table in a database and can appear in other tables as a result of its connection to the data in those other tables. Foreign keys are used when a primary key from one table appears in another table.
4. Key Integrity: In order to guarantee that an entity or record is uniquely or differently recognized in the database, SQL has a number of important constraints. The table may contain several keys, but it need only have one primary key. For example, Primary Key, Foreign Key, Unique Key, etc.

Keep reading for a detailed discussion of each integrity constraint in the next articles.