Database Constraints & Integrity

Overview

Database Constraints & Integrity ensure data accuracy, consistency, and validity in a database. Constraints are rules that restrict the type of data that can be stored, while integrity rules ensure relationships between data are maintained.

Constraints and integrity rules are fundamental to maintaining data quality and preventing invalid data from entering the database.

Types of Data Integrity

1. Entity Integrity

Entity Integrity ensures that each row in a table is uniquely identifiable. It's enforced through primary key constraints.

Rules:
- Every table must have a primary key
- Primary key cannot be NULL
- Primary key must be unique
- No duplicate primary key values

Example:
Students Table:
- StudentID (PRIMARY KEY) → Must be unique, cannot be NULL
- Ensures each student is uniquely identified

2. Referential Integrity

Referential Integrity ensures that relationships between tables remain valid. It's enforced through foreign key constraints.

Rules:
- Foreign key values must exist in referenced table
- Foreign key can be NULL (if allowed)
- Cannot delete parent if children exist (unless CASCADE)
- Cannot update parent key if children exist (unless CASCADE)

Example:
Orders Table:
- CustomerID (FOREIGN KEY) → Must exist in Customers table
- Ensures order belongs to valid customer

3. Domain Integrity

Domain Integrity ensures that column values are valid according to defined rules. It's enforced through data types, CHECK constraints, and NOT NULL constraints.

Products Table:
- Price DECIMAL(10,2) → Must be numeric
- Price CHECK (Price > 0) → Must be positive
- ProductName VARCHAR(200) NOT NULL → Cannot be empty
- Category IN ('Electronics', 'Books', 'Clothing') → Valid values only

4. User-Defined Integrity

User-Defined Integrity refers to business rules specific to an organization that are enforced through custom constraints and triggers.

Types of Constraints

1. Primary Key Constraint

Uniquely identifies each row in a table. Ensures entity integrity.

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100) NOT NULL,
    Email VARCHAR(100)
);

- StudentID uniquely identifies each student
- Cannot be NULL
- Must be unique

2. Foreign Key Constraint

Maintains referential integrity by linking tables together.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT NOT NULL,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

- CustomerID must exist in Customers table
- Ensures valid relationship

3. NOT NULL Constraint

Ensures a column cannot contain NULL values.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL,
    Email VARCHAR(100) NOT NULL,
    Phone VARCHAR(20)  -- Can be NULL
);

- Required fields cannot be empty
- Enforces mandatory data

4. UNIQUE Constraint

Ensures all values in a column are unique.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE,
    Phone VARCHAR(20) UNIQUE
);

- Email addresses must be unique
- Phone numbers must be unique

5. CHECK Constraint

Validates that column values meet specified conditions.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(200) NOT NULL,
    Price DECIMAL(10,2) CHECK (Price > 0),
    StockQuantity INT CHECK (StockQuantity >= 0),
    Category VARCHAR(50) CHECK (Category IN ('Electronics', 'Books', 'Clothing'))
);

- Price must be positive
- Stock cannot be negative
- Category must be from valid list

6. DEFAULT Constraint

Sets a default value for a column when no value is specified.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE DEFAULT CURRENT_DATE,
    Status VARCHAR(20) DEFAULT 'Pending',
    TotalAmount DECIMAL(10,2) DEFAULT 0.00
);

- Default values applied automatically
- Reduces need to specify common values

Integrity Rules in Action

Example: Enforcing Integrity

-- Parent Table
CREATE TABLE Departments (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(100) NOT NULL UNIQUE
);

-- Child Table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Salary DECIMAL(10,2) CHECK (Salary > 0),
    HireDate DATE DEFAULT CURRENT_DATE,
    DeptID INT NOT NULL,
    FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

Integrity Enforced:
✓ Entity: EmployeeID uniquely identifies employees
✓ Referential: DeptID must exist in Departments
✓ Domain: Salary must be positive, Email unique
✓ Default: HireDate set automatically

Referential Integrity Actions

When parent data is updated or deleted, foreign keys can specify behavior:

ON DELETE RESTRICT / NO ACTION

Prevents deletion of parent record if children exist (default behavior).

ON DELETE CASCADE

Automatically deletes child records when parent is deleted.

ON DELETE SET NULL

Sets foreign key to NULL when parent is deleted.

ON UPDATE CASCADE

Automatically updates foreign key when parent key changes.

Constraint Enforcement Levels

Constraints can be enforced at different levels:

• Database Level: Enforced by DBMS (primary keys, foreign keys)
• Application Level: Enforced by application code (business rules)
• Domain Level: Data type validation
• User Level: Input validation before data entry

Benefits of Constraints

• Data accuracy: Prevents invalid data entry
• Consistency: Ensures uniform data across database
• Reliability: Database enforces rules automatically
• Documentation: Constraints document business rules
• Error prevention: Catches errors before they cause problems

Best Practices

• Always use primary keys: Every table should have one
• Use foreign keys: Maintain referential integrity
• Set NOT NULL for required fields: Enforce mandatory data
• Add CHECK constraints: Validate data at database level
• Choose appropriate actions: CASCADE vs RESTRICT carefully
• Document business rules: Make constraints clear

Next Steps

Learn about improving query performance with Database Indexes, or explore managing concurrent access with Transactions.