Relational Integrity Constraints
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