Constraints in DBMS-
- Relational constraints are the restrictions imposed on the database contents and operations.
- They ensure the correctness of data in the database.
Types of Constraints in DBMS-
In DBMS, there are following 5 different types of relational constraints-
- Domain constraint
- Tuple Uniqueness constraint
- Key constraint
- Entity Integrity constraint
- Referential Integrity constraint
1. Domain Constraint-
- Domain constraint defines the domain or set of values for an attribute.
- It specifies that the value taken by the attribute must be the atomic value from its domain.
Example-
Consider the following Student table-
| STU_ID | Name | Age |
| S001 | Akshay | 20 |
| S002 | Abhishek | 21 |
| S003 | Shashank | 20 |
| S004 | Rahul | A |
Here, value ‘A’ is not allowed since only integer values can be taken by the age attribute.
2. Tuple Uniqueness Constraint-
Tuple Uniqueness constraint specifies that all the tuples must be necessarily unique in any relation.
Example-01:
Consider the following Student table-
| STU_ID | Name | Age |
| S001 | Akshay | 20 |
| S002 | Abhishek | 21 |
| S003 | Shashank | 20 |
| S004 | Rahul | 20 |
This relation satisfies the tuple uniqueness constraint since here all the tuples are unique.
Example-02:
Consider the following Student table-
| STU_ID | Name | Age |
| S001 | Akshay | 20 |
| S001 | Akshay | 20 |
| S003 | Shashank | 20 |
| S004 | Rahul | 20 |
This relation does not satisfy the tuple uniqueness constraint since here all the tuples are not unique.
3. Key Constraint-
Key constraint specifies that in any relation-
- All the values of primary key must be unique.
- The value of primary key must not be null.
Example-
Consider the following Student table-
| STU_ID | Name | Age |
| S001 | Akshay | 20 |
| S001 | Abhishek | 21 |
| S003 | Shashank | 20 |
| S004 | Rahul | 20 |
This relation does not satisfy the key constraint as here all the values of primary key are not unique.
4. Entity Integrity Constraint-
- Entity integrity constraint specifies that no attribute of primary key must contain a null value in any relation.
- This is because the presence of null value in the primary key violates the uniqueness property.
Example-
Consider the following Student table-
| STU_ID | Name | Age |
| S001 | Akshay | 20 |
| S002 | Abhishek | 21 |
| S003 | Shashank | 20 |
| Rahul | 20 |
This relation does not satisfy the entity integrity constraint as here the primary key contains a NULL value.
5. Referential Integrity Constraint-
- This constraint is enforced when a foreign key references the primary key of a relation.
- It specifies that all the values taken by the foreign key must either be available in the relation of the primary key or be null.
Read more- Foreign Key in DBMS
Important Results-
The following two important results emerges out due to referential integrity constraint-
- We can not insert a record into a referencing relation if the corresponding record does not exist in the referenced relation.
- We can not delete or update a record of the referenced relation if the corresponding record exists in the referencing relation.
Example-
Consider the following two relations- ‘Student’ and ‘Department’.
Here, relation ‘Student’ references the relation ‘Department’.
Student
| STU_ID | Name | Dept_no |
| S001 | Akshay | D10 |
| S002 | Abhishek | D10 |
| S003 | Shashank | D11 |
| S004 | Rahul | D14 |
Department
| Dept_no | Dept_name |
| D10 | ASET |
| D11 | ALS |
| D12 | ASFL |
| D13 | ASHS |
Here,
- The relation ‘Student’ does not satisfy the referential integrity constraint.
- This is because in relation ‘Department’, no value of primary key specifies department no. 14.
- Thus, referential integrity constraint is violated.
Handling Violation of Referential Integrity Constraint-
To ensure the correctness of the database, it is important to handle the violation of referential integrity constraint properly.
Next Article- Handling Violation of Referential Integrity Constraint
Get more notes and other study material of Database Management System (DBMS).
Watch video lectures by visiting our YouTube channel LearnVidFun.
