Relational Integrity Constraints

Facebooktwitterredditpinterestlinkedinmailby feather

Relational Integrity Constraints are the set of rules that can be used to maintain the data integrity during an insert, delete and update operations into a table. These constraints are checked in the database before performing any operation such as insertion, deletion, and updation on the tables.

There are four main types of relational integrity constraints:

  • Key Constraint
  • Domain Constraint
  • Entity Integrity Constraint
  • Referential Integrity Constraint

1. Key Constraint

Every table or relation in a database should have at least one attribute or a set of attributes which uniquely identifies a record. Those attributes or a set of attributes is called key.
For example, rollno in the table ‘Student’ is a key. Two students cannot have the same roll number. So, a key has two features:

It cannot have two values.

It should be unique for all the tuples.

2. Domain Constraint

Domain constraints are the attribute level constraints. A domain is a collection of possible values for an attribute in the database table. An attribute or a field in a table can accept only those values which are inside the domain range.
For example, in the below table Student, the rollno attribute has an integer domain so that an attribute or a field cannot accept values that are not integers,i.e., rollno cannot have values like10.11, ‘first’, etc.

3. Entity Integrity Constraint

Entity constraint specifies that the value of the primary key cannot be NULL. Because, if the primary key has a NULL value, you cannot identify those rows. A field other than the primary key field in a relation can have a NULL value.

Example: In the below table, Student, suppose rollno is the primary key. Thus, from the definition of Entity Integrity constraint, the value of rollno cannot be null as it uniquely identifies each record in the table.

Table: Student

rollno Name City Age
101 Akhil Meerut 20
102 Chetan Delhi 22
103 Anubhav Ghaziabad 21
104 Rishabh Shivam 20

4. Referential Integrity Constraint

A referential integrity constraint is a specified relationship between two tables. It can be defined as when one field of a relation in a database can take the values from another field in the same table or another table. The relationship between the database tables is established by the foreign key.

Example:
Consider two relations Student and Course where rollno is the primary key in the Student table and foreign key in the Course table.

rollno Name City Age
101 Akhil Meerut 20
102 Chetan Delhi 22
103 Anubhav Ghaziabad 21
104 Rishabh Shivam 20

Table: Student

Course_id Course_name rollno
C1 MCA 101
C2 MBA 102
C3 MCA 103
C4 MCA 104

Table: Course

Facebooktwitterredditpinterestlinkedinmailby feather