Types of Constraints in DBMS

Types of Constraints in DBMS

When modelling the design of a relational database, we can include constraints such as what values can be inserted into the relation and what types of modifications and deletions are permitted. These are the constraints we place on the relational database.

Types of Constraints in DBMS

Database constraints can be divided into three categories:

  • Implicit constraints are constraints that are applied in the data model.
  • Constraints that are directly applied in the data model schemas by specifying them in the DDL (Data Definition Language). These are known as schema-based or explicit constraints.
  • Constraints that cannot be directly applied in the data model’s schemas. These are known as application-based or semantic constraints.
Types of Constraints in DBMS

So here we will deal with Implicit constraints

Mainly Constraints on the relational database are of 5 types: 

  1. Domain constraints
  2. Key constraints
  3. Entity Integrity constraints
  4. Referential integrity constraints
  5. Tuple Uniqueness constraint

Types of Constraints in DBMS

Domain constraints 

  1. Because every domain must have atomic values (the smallest indivisible units), composite and multi-valued attributes are not permitted.

  2. We perform a datatype check here, which means that when we assign a data type to a column, we limit the values that can be stored in it. For example, if we assign the datatype of attribute age to int, we cannot assign values other than int datatype.

Here, value ‘A’ is not allowed since only integer values can be taken by the age attribute.

Domain constraints

Uniqueness Constraints or Key Constraints:

  1. These are known as uniqueness constraints because they ensure that each tuple in the relation is unique.
  2. A relation can have multiple keys or candidate keys (minimal superkeys), from which we select one as the primary key. There are no restrictions on selecting the primary key from candidate keys, but it is recommended to select the candidate key with the fewest attributes.
  3. Because null values are not permitted in the primary key, the Not Null constraint is also part of the key constraint.

In the below table, STU_ID is the primary key, and first and the last tuple has the same value in STU_ID ie S001, so it is violating the key constraint. 

Key Constrains

Constraints on Entity Integrity

  1. Entity Integrity constraints state that no primary key can have a NULL value because primary keys are used to uniquely identify each tuple in a relation.
    Example:
  2. Explanation: In the preceding relation, EID is made the primary key, and the primary key cannot accept NULL values, but in the third tuple, the primary key is null, indicating that Entity Integrity constraints are being violated.
Entity Integrity

Referential Integrity Constraints 

  1. The Referential integrity constraints are specified between two relations or tables and are used to keep the tuples in two relations consistent.
  2. When an attribute in the foreign key of relation R1 has the same domain(s) as the primary key of relation R2, then the foreign key of R1 is said to reference or refer to the primary key of relation R2.
  3. The values of the foreign key in a tuple of relation R1 can either be the values of the primary key for some tuple in relation R2, or they can be NULL, but they cannot be empty.
Referential Integrity Constraints

The DNO of the first relation is the foreign key, while the DNO of the second relation is the primary key. DNO = 22 in the first table’s foreign key is not permitted because DNO = 22 is not defined in the second relation’s primary key. As a result, the referential integrity constraints are violated here.