Explain Entity Integrity Constraint & Referential Integrity Constraints. Why is each important in a database?

Explain Entity Integrity Constraint & Referential Integrity Constraints. Why is each important in a database?

Answer:-

---

1. Entity Integrity Constraint

Definition:

The Entity Integrity Constraint states that:

• The primary key of a relation (table) cannot be NULL.
• Every row in a table must have a unique, non-null identifier.

Why It’s Important:

• Ensures that each record can be uniquely identified.
• Prevents creation of duplicate or unidentified rows.
• Supports data accuracy and consistency.

Example:

CREATE TABLE Students (
  RollNo INT PRIMARY KEY,
  Name VARCHAR(50)
);

• Here, RollNo cannot be NULL or duplicate. It uniquely identifies each student.

---

2. Referential Integrity Constraint

Definition:

The Referential Integrity Constraint ensures that:

• A foreign key in one table must match a primary key in another table.
• Or it must be NULL (if allowed).

Why It’s Important:

• Maintains valid relationships between tables.
• Prevents creation of orphan records (e.g., an order for a non-existent customer).
• Ensures data consistency across related tables.

Example:

CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  CustomerID INT,
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

• This ensures that every CustomerID in Orders must exist in the Customers table.

---

Summary:

Constraint Type	Purpose	Key Rule	Importance
Entity Integrity	Uniquely identifies each row	Primary key cannot be NULL	Ensures unique and complete records
Referential Integrity	Maintains valid relationships between tables	Foreign key must match referenced key	Prevents broken or invalid references