Integrity constraints are fundamental rules in database management systems that ensure data accuracy and consistency. Without these constraints, databases can contain invalid, inconsistent, or corrupted data. Let's examine a simple student records table to understand why constraints are essential. Here we can see several data integrity issues: duplicate IDs, invalid age values, and improperly formatted email addresses. Integrity constraints prevent such problems by enforcing rules at the database level. The main types include domain constraints that validate data types and ranges, entity constraints that ensure record uniqueness, key constraints that maintain unique identification, and referential constraints that preserve relationships between tables.
Domain and entity constraints form the foundation of data integrity. Domain constraints enforce rules about individual data values, including data type enforcement, value range restrictions, and format validation. For example, age values must be between 0 and 120, email addresses must follow proper format, and grades must be from the set A, B, C, D, or F. Entity constraints ensure that each record in a table is unique and complete. This includes primary key uniqueness and NOT NULL requirements for essential fields. Let's examine this student data validation example. The first row shows valid data that passes all constraints. However, we can see several violations: an age value of 150 exceeds the valid range, an improperly formatted email address, a grade value X that's not in the allowed domain, and a NULL name which violates the NOT NULL constraint. These constraints work together to maintain data quality and prevent invalid information from entering the database.
Key constraints are essential for maintaining unique identification of records in database tables. A primary key uniquely identifies each row and cannot contain NULL values. Each table can have only one primary key. Candidate keys are columns or combinations of columns that could serve as primary keys. Unique constraints also ensure uniqueness but allow NULL values, unlike primary keys. Composite keys use multiple columns together to form a unique identifier. Let's examine an order details table where OrderID and ProductID together form a composite primary key. This allows the same order to contain multiple products, and the same product to appear in multiple orders, but prevents duplicate entries for the same product in the same order. Notice how the duplicate entry violates the composite key constraint. In contrast, the employee table shows the difference between primary keys and unique constraints. The employee ID serves as the primary key and cannot be NULL, while the social security number has a unique constraint that permits NULL values for employees who haven't provided this information yet.
Referential integrity constraints maintain relationships between tables through foreign keys. A foreign key in one table must reference a valid primary key in another table, creating parent-child relationships. This prevents orphaned records and maintains data consistency across related tables. Let's examine a student enrollment system. The Students table serves as the parent table with StudentID as the primary key. The Enrollments table is the child table with StudentID as a foreign key that must reference valid student records. Valid enrollments show proper foreign key relationships with arrows connecting child records to their parent records. However, enrollment E004 attempts to reference student S999 which doesn't exist, creating a referential integrity violation. Database systems provide several referential actions to handle updates and deletions. CASCADE automatically propagates changes to child records. RESTRICT prevents operations that would violate referential integrity. SET NULL sets foreign key values to NULL when parent records are deleted. These mechanisms ensure that relationships between tables remain consistent and prevent data inconsistencies that could compromise database integrity.
Let's examine a comprehensive university database system where multiple constraint types work together to maintain data integrity. This system includes students, courses, enrollments, and grades tables with complex relationships and business rules. When a student attempts to enroll in a course, the system must check multiple constraints: the student must exist in the students table, the course must be available, capacity limits must not be exceeded, and any prerequisites must be satisfied. Grade assignment requires that the student is actually enrolled in the course and that the grade value is from the valid domain. The database performs constraint checking in a specific order during INSERT operations. First, domain constraints validate data types and value ranges. Next, entity constraints ensure primary keys are unique and not null. Then key constraints verify uniqueness requirements. Finally, referential integrity constraints check that foreign keys reference valid parent records. Here we see a violation example where enrollment E04 attempts to reference student S99 who doesn't exist in the students table. This demonstrates how referential integrity constraints prevent orphaned records and maintain database consistency. The interconnected nature of these constraints ensures that the database remains in a valid state even as complex operations are performed across multiple related tables.