Week 4
4.3 Integrity Constraints
Integrity constraints are rules that ensure the accuracy, validity, and consistency of data in the database. The two most fundamental integrity constraints in the relational model are:
4.3.1 Entity Integrity Constraint
RULE: No attribute that forms part of the PRIMARY KEY of a base relation may accept a NULL value.
RATIONALE: The primary key is used to uniquely identify each tuple. If it were NULL, we could not identify the tuple, defeating the entire purpose of the primary key.
|
Entity Integrity Example: In the STUDENT table, MatricNo is the primary key. The entity integrity constraint ensures that: - You CANNOT insert a student record without a MatricNo - You CANNOT insert TWO students with the same MatricNo - MatricNo can NEVER be NULL SQL Implementation: MatricNo VARCHAR(20) NOT NULL PRIMARY KEY |
4.3.2 Referential Integrity Constraint
RULE: If a foreign key exists in a relation, either the foreign key value must match a primary key value in the referenced relation, or the foreign key value must be NULL.
RATIONALE: Foreign keys create links between tables. Referential integrity ensures these links are always valid, you cannot reference a record that doesn't exist.
|
Referential Integrity Example: STUDENT table has DeptCode as a foreign key referencing DEPARTMENT(DeptCode). The referential integrity constraint ensures: - You CANNOT insert a student with DeptCode='CHEM' if 'CHEM' doesn't exist in DEPARTMENT - You CANNOT delete a department if students still reference it (without cascading rules) SQL: DeptCode VARCHAR(10) REFERENCES DEPARTMENT(DeptCode) ON DELETE RESTRICT |
CASCADE ACTIONS on referential integrity violation:
1. ON DELETE CASCADE — When a referenced row is deleted, delete all referencing rows
2. ON DELETE SET NULL — When a referenced row is deleted, set FK to NULL
3. ON DELETE RESTRICT — Prevent deletion if referenced rows exist (most conservative)
4. ON DELETE SET DEFAULT — Set FK to a default value when referenced row is deleted