Integrity Constraints in DBMS
Integrity constraints are rules in a Database Management System (DBMS) that ensure the quality and accuracy of the data stored. They play a critical role in maintaining the integrity of the database, preventing accidental or intentional corruption of the data. These constraints ensure that when data is inserted, updated, or deleted, it does not violate predefined rules. This protection preserves the integrity of the database.
In this blog, we will explore different types of integrity constraints, explain their importance, and illustrate them with examples in a tabular format.
1. Domain Constraints
Domain constraints restrict the data values that can be entered into a column (or attribute). It ensures that all the values in an attribute must fall within a defined set or domain. The domain defines acceptable data types, such as integers, characters, dates, etc. For example, if an attribute is defined as an integer, it should not accept any other data type like strings or dates.
Example:
Let's take an example where we have a STUDENT table with attributes STUDENT_ID and AGE. The domain constraint ensures that the AGE column must only store integer values between 18 and 25.
| STUDENT_ID |
AGE |
| 101 |
20 |
| 102 |
19 |
| 103 |
26 |
| 104 |
21 |
Here, entering a value like 26 will violate the domain constraint if the age limit is restricted to between 18 and 25.
2. Entity Integrity Constraints
The entity integrity constraint ensures that each record in a table is uniquely identifiable by its primary key, and this primary key should never be NULL. The reason for this is simple: the primary key is used to differentiate records in the table, and if it is missing or NULL, there would be ambiguity in identifying records.
Example:
Consider an EMPLOYEE table with a primary key EMP_ID:
| EMP_ID |
EMP_NAME |
DEPARTMENT |
| 501 |
Alice |
Finance |
| 502 |
Bob |
HR |
| NULL |
Charlie |
Marketing |
In this case, EMP_ID cannot be NULL because it is the primary key, and that would violate the entity integrity constraint.
3. Referential Integrity Constraints
Referential integrity ensures that the relationships between tables remain consistent. If a table contains a foreign key, the values of that foreign key must either be NULL or correspond to existing values in the related table's primary key.
Example:
Let’s assume we have two tables, ORDERS and CUSTOMERS. The foreign key CUSTOMER_ID in the ORDERS table should correspond to a valid CUSTOMER_ID in the CUSTOMERS table.
CUSTOMERS Table:
| CUSTOMER_ID |
CUSTOMER_NAME |
| 201 |
John Doe |
| 202 |
Jane Smith |
ORDERS Table:
| ORDER_ID |
CUSTOMER_ID |
ORDER_DATE |
| 301 |
201 |
2024-01-05 |
| 302 |
203 |
2024-01-10 |
Here, ORDER_ID 302 violates the referential integrity because CUSTOMER_ID 203 does not exist in the CUSTOMERS table.
4. Key Constraints
Key constraints ensure that attributes that are defined as keys (primary key, unique key, etc.) must have unique values. A key constraint allows no duplicate values for the column and, in the case of a primary key, no NULL values either.
Example:
Consider a PRODUCTS table, where PRODUCT_ID is defined as a primary key, and PRODUCT_CODE must also be unique.
| PRODUCT_ID |
PRODUCT_CODE |
PRODUCT_NAME |
| 101 |
P-001 |
Laptop |
| 102 |
P-002 |
Smartphone |
| 103 |
P-001 |
Tablet |
In this case, having the same PRODUCT_CODE (P-001) for multiple products would violate the unique key constraint, as each PRODUCT_CODE should be unique.
Importance of Integrity Constraints
The significance of integrity constraints cannot be overstated in database management:
- Data Accuracy: These rules ensure that only valid data is entered into the system, reducing the chance of incorrect or incomplete data being stored.
- Data Consistency: By maintaining relationships between tables and ensuring that primary and foreign key rules are followed, integrity constraints preserve the consistency of data.
- Error Prevention: Constraints act as a safeguard against accidental or malicious data corruption by preventing invalid data entries.
- Efficient Querying: Enforcing constraints ensures that queries return accurate results since the data adheres to predefined rules.
Conclusion
Integrity constraints are essential for preserving the quality and integrity of a database. Domain constraints prevent inappropriate data entries, entity integrity ensures uniqueness of records, referential integrity maintains relationships between tables, and key constraints prevent duplication of key attributes. By enforcing these constraints, DBMS ensures that the data remains accurate, consistent, and free from errors.
Understanding and applying integrity constraints is a vital step in designing and maintaining a reliable database, and they should always be considered during database design and implementation.
