←Back to Subject
Notes
Notes
Study material and explanations
Diagrams
Diagrams
Visualize concepts easily
Mnemonics
Mnemonics
Tricks to remember faster
One Night Prep
One Night Prep
Short and focused revision
Revision
Revision
Quick summary and key points
Important Questions
Important Questions
Practice most relevant questions
Notes

Relational Integrity Constraints: Entity, Referential & Domain

Study material and explanations for Unit 2

Relational Integrity Constraints: Entity, Referential, and Domain Rules

What are Integrity Constraints? (In Simple Words)

In a database, Integrity means Accuracy, Consistency, and Correctness of data.

Integrity Constraints are predefined security rules and checks enforced by the DBMS engine to prevent users or buggy application programs from inserting erroneous, invalid, or corrupted records into tables.

1. The Three Fundamental Relational Integrity Constraints

The relational model enforces three primary categories of integrity constraints:

The Three Pillars of Relational Integrity
1. Domain Integrity
Every value in a column must be valid according to its defined data type, format, and range.
2. Entity Integrity
No primary key column can ever contain a NULL value or duplicate value.
3. Referential Integrity
Every foreign key value must either match an existing primary key in the parent table or be NULL.

2. Deep Dive: Domain Integrity Constraints

  • Ensures that every column contains valid, atomic values conforming to its defined Data Domain.
  • Checks Enforced:
    1. Data Type Check: Trying to enter "Rahul" into an Age column (defined as INT) is immediately blocked by the database engine.
    2. Range Check: Restricting values using CHECK constraints (e.g., CHECK (Marks >= 0 AND Marks <= 100)).
    3. Format / Pattern Check: Ensuring email addresses contain an @ symbol or phone numbers contain exactly 10 digits.
    4. Default Value Check: Assigning automatic fallback values when a user leaves a field blank (e.g., DEFAULT 'Pending').

3. Deep Dive: Entity Integrity Constraint

  • Formal Rule: No attribute that participates in the primary key of a base relation can accept a NULL value.
  • Why is this necessary?
    • The Primary Key is the sole hardware identifier used to distinguish individual real-world objects in a relation.
    • If a primary key attribute were NULL, the DBMS would have no way to locate, distinguish, or link that specific row!
  • Example:
    • In table STUDENT(Roll_No, Name, Email) where Roll_No is the primary key:
    • Inserting INSERT INTO STUDENT VALUES (NULL, 'Priya', '[email protected]'); will immediately throw an Entity Integrity Violation Error.

4. Deep Dive: Referential Integrity Constraint (Foreign Key Rules)

  • Formal Rule: If a foreign key FK exists in relation R1 referencing primary key PK of relation R2, then for every tuple in R1, the value of FK must either be equal to the PK value of some tuple in R2, or be NULL.
  • Prevents Orphan Records (e.g., a student record assigned to a department Dept_ID = 99 when no department 99 exists!).
Referential Integrity Link Between Tables
Parent Table: DEPARTMENT
Dept_ID = 10 (Computer Science)
➔ Referenced By ➔
Child Table: STUDENT
Roll_No: 101, Name: 'Aman', Dept_ID: 10

What Happens When a Parent Record is Deleted or Updated?

Suppose the college decides to delete DEPARTMENT 10. What happens to all the students enrolled in department 10? The database designer can configure one of four referential actions:

Action Keyword What Happens in the Database? Real-World Use Case
1. ON DELETE RESTRICT (or NO ACTION) Rejects the deletion! The DBMS blocks the user from deleting Department 10 as long as student rows exist. (Default behavior). Preventing accidental deletion of university branches while students are enrolled.
2. ON DELETE CASCADE Cascades the deletion! Automatically deletes Department 10 AND automatically deletes all students belonging to that department! When an Order is deleted, all its Order_Items should be automatically deleted.
3. ON DELETE SET NULL Deletes Department 10, but automatically updates the Dept_ID of affected students to NULL. When a manager resigns, their subordinates have their Manager_ID set to NULL temporarily.
4. ON DELETE SET DEFAULT Deletes Department 10, but automatically sets affected students to a predefined fallback department (e.g., Department 0 - “Unassigned”). Moving orphan items to a general holding department.

5. Summary Table of Integrity Constraints

Constraint Name Applies To Rule Enforced Typical Violation
Domain Integrity Columns / Attributes Values must conform to data type, format, range Entering alphabets into an integer field
Entity Integrity Primary Keys Primary key must be unique and NOT NULL Inserting a duplicate roll number or NULL
Referential Integrity Foreign Keys Foreign key must match an existing parent key or be NULL Assigning an employee to a non-existent department
Key Constraint Candidate Keys All values in the key column must be strictly unique Registering two students with the exact same Aadhar number

💡 DAVV Exam Preparation Tips

5-Mark Question: “What is Referential Integrity? Explain with an example of Parent and Child tables.”

  • Define the foreign key rule clearly.
  • Draw a small DEPARTMENT (parent) and EMPLOYEE (child) table.
  • Explain why entering a non-existent Dept_ID in EMPLOYEE triggers an error.

10-Mark Question: “Explain Entity Integrity, Domain Integrity, and Referential Integrity constraints in detail. Discuss the four options available under ON DELETE and ON UPDATE actions (RESTRICT, CASCADE, SET NULL, SET DEFAULT).”

  • Provide clear definitions and SQL constraint syntax for all three.
  • Explain the concept of orphan records.
  • Give thorough real-world explanations of ON DELETE CASCADE vs RESTRICT vs SET NULL.