Integrity Constraints and Keys

Keys and constraints are the backbone of the relational model. They ensure that every row is unique, every relationship is valid, and every value follows the rules.

Without keys, data becomes a chaotic pile of records. Without constraints, bad data creeps in silently.

This chapter covers every type of key in DBMS and the integrity constraints that enforce correctness.

---

Learning Objectives

After completing this chapter, you will be able to:

• Define and differentiate all six types of keys.
• Explain entity integrity, referential integrity, and domain constraints.
• Understand when to use each key type.
• Determine primary keys and foreign keys from requirements.
• Recognize violations of referential integrity.
• Answer interview questions about keys and constraints.

---

What is a Key?

A Key is an attribute or a set of attributes that uniquely identifies a row in a table.

In a database, every row must be distinguishable from every other row.

Keys make this possible.

---

Types of Keys

1. Super Key

A Super Key is any set of attributes that can uniquely identify a row.

It may contain extra (redundant) attributes.

Example

Student table:

Student_ID	Name	Email	Phone
101	Rahul	rahul@mail.com	9876543210

Super Keys:

• {Student_ID} — unique, no redundancy.
• {Email} — unique, no redundancy.
• {Student_ID, Name} — unique, but Name is redundant.
• {Email, Phone} — unique, both needed together.
• {Student_ID, Email, Phone} — unique, but Phone and Email are redundant.

Any combination that can identify a single row is a Super Key.

---

2. Candidate Key

A Candidate Key is a minimal Super Key.

No subset of a candidate key can uniquely identify a row.

Example

From the Student table:

• {Student_ID} — Candidate Key.
• {Email} — Candidate Key.
• {Student_ID, Name} — NOT a Candidate Key because {Student_ID} alone is enough.

Candidate Keys are the “best” unique identifiers.

---

3. Primary Key

The Primary Key is the Candidate Key chosen by the database designer as the main identifier.

Characteristics:

• Uniquely identifies every row.
• Cannot be NULL (entity integrity).
• Only one per table.
• Usually chosen for performance and stability.

Selection Criteria

Factor	Why It Matters
Stable	Should never change (no social security numbers — people change them).
Unique	Must always be unique.
Simple	Prefer a single column over composite.
Non-NULL	Every row must have a value.

Best practice: Use synthetic keys (auto-increment integers or UUIDs) instead of natural keys.

---

4. Alternate Key

Alternate Keys are Candidate Keys that were NOT chosen as the Primary Key.

Example

Student table:

• Candidate Keys: {Student_ID}, {Email}
• Primary Key: {Student_ID}
• Alternate Key: {Email}

Alternate Keys are still unique — you can enforce uniqueness with a UNIQUE constraint.

---

5. Foreign Key

A Foreign Key is an attribute in one table that references the Primary Key of another table.

It creates a relationship between the two tables.

Example

Students                        Enrollments
┌──────────────┐               ┌──────────────────────┐
│ Student_ID   │←── FK ─────   │ Enrollment_ID        │
│ Name         │               │ Student_ID (FK)      │
│ Email        │               │ Course_ID            │
└──────────────┘               │ Enrollment_Date      │
                               └──────────────────────┘

The Student_ID in Enrollments is a Foreign Key pointing to Students.Student_ID.

Rules

• A Foreign Key value must either match an existing Primary Key value in the parent table, or be NULL.
• This is called Referential Integrity.

---

6. Composite Key

A Composite Key is a key that consists of two or more attributes.

It is used when no single attribute can uniquely identify a row.

Example

Enrollments
┌──────────────────────┐
│ Student_ID (PK part) │
│ Course_ID (PK part)  │
│ Enrollment_Date      │
└──────────────────────┘

Neither Student_ID alone nor Course_ID alone is unique (a student takes many courses, a course has many students).

But the combination {Student_ID, Course_ID} is unique.

---

Visual Hierarchy of Keys

        Super Key (any set that uniquely identifies a row)
             │
             ▼
      Candidate Key (minimal Super Key)
          /        \
         /          \
        ▼            ▼
  Primary Key    Alternate Keys
  (chosen one)   (not chosen)

  Foreign Key — references Primary Key of another table
  Composite Key — key with multiple attributes

---

Integrity Constraints

Integrity Constraints are rules enforced by the DBMS to ensure data accuracy, consistency, and validity.

There are three main types.

---

1. Domain Constraints

Domain Constraints ensure that values in a column belong to a valid set.

Rules

• Data type must match (INT, VARCHAR, DATE).
• Value must satisfy CHECK constraints.
• Value must be within ENUM or SET options.

Examples

Age INT CHECK (Age > 0 AND Age < 150)
Gender CHAR(1) CHECK (Gender IN ('M', 'F', 'O'))
Email VARCHAR(255) UNIQUE

What Happens on Violation

The DBMS rejects the INSERT or UPDATE.

INSERT INTO Students (Name, Age) VALUES ('Rahul', -5);
-- ERROR: CHECK constraint violation: Age must be > 0

---

2. Entity Integrity

Entity Integrity ensures that the Primary Key cannot be NULL (or partially NULL for composite keys).

Why

If a Primary Key is NULL, the row cannot be uniquely identified.

If part of a composite primary key is NULL, the uniqueness guarantee breaks.

Example

CREATE TABLE Students (
    Student_ID INT PRIMARY KEY,  -- Cannot be NULL
    Name VARCHAR(100)
);

INSERT INTO Students (Name) VALUES ('Rahul');
-- ERROR: Primary Key cannot be NULL

What Happens on Violation

The INSERT fails.

---

3. Referential Integrity

Referential Integrity ensures that a Foreign Key value must either:

1. Match an existing Primary Key in the parent table.
2. Be NULL (if the column allows NULLs).

Example

CREATE TABLE Students (
    Student_ID INT PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE TABLE Enrollments (
    Enrollment_ID INT PRIMARY KEY,
    Student_ID INT,
    FOREIGN KEY (Student_ID) REFERENCES Students(Student_ID)
);

Valid Operations

INSERT INTO Students VALUES (1, 'Rahul');
INSERT INTO Enrollments VALUES (101, 1);  -- Valid: Student_ID 1 exists
INSERT INTO Enrollments VALUES (102, NULL);  -- Valid: NULL allowed

Violations

INSERT INTO Enrollments VALUES (103, 999);  -- ERROR: Student_ID 999 does not exist

---

Foreign Key Actions

When a parent row is deleted or updated, the DBMS can:

Action	Behavior
RESTRICT (default)	Prevent deletion if FK references exist.
CASCADE	Delete/update the child rows too.
SET NULL	Set child FK to NULL.
SET DEFAULT	Set child FK to a default value.
NO ACTION	Similar to RESTRICT (check at end of transaction).

Example

FOREIGN KEY (Student_ID) REFERENCES Students(Student_ID)
ON DELETE CASCADE
ON UPDATE CASCADE

If a student is deleted, all their enrollments are also deleted.

---

Real-World Scenario

Consider an e-commerce database.

CREATE TABLE Customers (
    Customer_ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(255) UNIQUE
);

CREATE TABLE Orders (
    Order_ID INT PRIMARY KEY,
    Customer_ID INT,
    Order_Date DATE,
    Total DECIMAL(10,2),
    FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID)
        ON DELETE RESTRICT
);

CREATE TABLE Order_Items (
    Order_ID INT,
    Product_ID INT,
    Quantity INT,
    Price DECIMAL(10,2),
    PRIMARY KEY (Order_ID, Product_ID),
    FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID)
        ON DELETE CASCADE
);

What each constraint does:

1. Customer_ID in Orders is a FK — you cannot create an order for a non-existent customer.
2. ON DELETE RESTRICT — you cannot delete a customer who has orders.
3. Order_Items has a composite PK — each product appears once per order.
4. ON DELETE CASCADE — if an order is deleted, its items are deleted automatically.

---

Interview Deep Dive

---

Key Takeaways

• Super Key is any set of attributes that uniquely identifies a row.
• Candidate Key is a minimal Super Key.
• Primary Key is the chosen Candidate Key (non-NULL, unique, one per table).
• Alternate Keys are candidate keys not chosen as primary.
• Foreign Key links tables by referencing another table’s primary key.
• Composite Key uses multiple attributes for uniqueness.
• Domain constraints enforce valid values for columns.
• Entity integrity ensures primary keys cannot be NULL.
• Referential integrity ensures foreign key values are valid.
• Foreign key actions (RESTRICT, CASCADE, SET NULL) determine behavior on delete/update.