Integrity Constraints in DBMS - Types & Example
Integrity constraints in DBMS are crucial for ensuring data accuracy and consistency. This article explores various types such as domain, entity, referential, and key constraints with examples. Understanding these constraints is essential for maintaining reliable databases.
1. Domain Constraint
2. Entity Integrity Constraint
3. Referential Integrity Constraint
4. Key Constraints
Integrity Constraints
- Integrity constraints are set of rules that the database is not permitted to violate.
- Integrity constraints are used to ensure accuracy and consistency of the data in a relational database.
- Constraints may apply to each attribute or they may apply to relationships between tables.
- Integrity constraints ensure that changes(update, deletion, insertion) made to the database by authorized users do not result in a loss of data consistency. Thus, integrity constraints guard against accidental damage to the database.
Example: A blood group must be 'A' or 'B' or 'AB' or 'O' only (can not any other values else).
Types of Integrity Constraint
2. Entity Integrity Constraint
3. Referential Integrity Constraint
4. Key Constraints
1. Domain Constraints
- Domain constraints defines the domain or the valid set of values for an attribute.
- The data type of domain includes string, character, integer, time, date, currency, etc. The value of the attribute must be available in the corresponding domain.
Example:
| ID | NAME | SEMESTER | AGE |
|---|---|---|---|
| 1000 | Tom | 1 | 19 |
| 1001 | Johnson | 2 | 24 |
| 1002 | Morgan | 5 | 23 |
| 1003 | David | 3 | 21 |
| 1004 | Susan | 8 | A |
Here, 'A' is not allowed. Because AGE is an integer attribute.
2. Entity Integrity Constraints
- The entity integrity constraint states that primary key value can't be null.
- This is because the primary key value is used to identify individual rows in relation and if the primary key has a null value, then we can't identify those rows.
- A table can contain a null value other than the primary key field.
Example:
| EMP_ID | EMP_NAME | SALARY |
|---|---|---|
| 123 | Jack | 30000 |
| 142 | Harry | 42000 |
| 164 | John | 25000 |
| Tom | 22000 |
Here, Empty(Null) value is not allowed as primary key can't contain a NULL value.
3. Referential Integrity Constraints
- A referential integrity constraint is specified between two tables.
- Referential integrity constraint is enforced when a foreign key references the primary key of a table.
- In the Referential integrity constraints, if a foreign key in Table 1 refers to the Primary Key of Table 2, then either every value of the Foreign Key in Table 1 must be available in primary key value of Table 2 or it must be null.
The Rules are:
- You can't delete a record from a primary table if matching records exist in a related table.
- You can't change a primary key value in the primary table if that record has related records.
- You can't insert a value in the foreign key field of the related table that doesn't exist in the primary key of the primary table.
- However, you can enter a Null value in the foreign key, specifying that the records are unrelated.
Example:
4. Key Constraints
- An entity set can have multiple keys or candidate keys (minimal superkey), but out of which one key will be the primary key.
- Key constraint specifies that in any relation - All the values of primary key must be unique and not be Null.
Example:
| ID | NAME | SAMESTER | AGE |
|---|---|---|---|
| 1000 | Jack | 3 | 17 |
| 1001 | Harry | 2 | 24 |
| 1002 | John | 5 | 22 |
| 1003 | Tom | 8 | 19 |
| 1002 | Kate | 7 | 21 |
Here, '1002' is Not allowed. Because all row must be unique.
Conclusion
In conclusion, integrity constraints in DBMS play an essential role in keeping our data accurate and reliable. By setting rules that the data must follow, these constraints help prevent errors and ensure everything is consistent. Whether it's domain, entity, referential, or key constraints, each type has a unique function in maintaining the database's integrity. Understanding and applying these constraints is essential for anyone working with databases, ensuring they remain dependable and error-free.
