DB Contraints

In any relational database, simply storing data is not enough. The data must also remain accurate, consistent, and reliable throughout the lifetime of the application.

Consider the following scenarios:

• Multiple users register with the same email address.
• A post is created for a user who does not exist.
• Important fields such as name or email are left empty.
• Invalid values such as negative account balances are stored.

Such situations can lead to data inconsistency, application errors, and unreliable business operations.

To prevent these issues, relational databases provide constraints.

Database constraints act as rules that control what data can be stored, ensuring that the database always maintains valid and meaningful information.

What are Database Constraints?

Database Constraints are rules enforced by a database on tables and columns to ensure data integrity and consistency.

They define what kind of data is allowed and restrict invalid operations that could compromise the quality of stored information.

Definition

A database constraint is a rule that restricts the values that can be inserted, updated, or deleted in a table to maintain data integrity and reliability.

Unlike application-level validations, database constraints work directly at the database layer, providing protection regardless of which application or service accesses the data.

Why Constraints are Important

In modern systems, multiple applications, microservices, APIs, and users may interact with the same database.

If validation exists only in application code:

• Different applications may implement different validation rules.
• Bugs may bypass validations.
• Direct database operations may introduce invalid data.

Database constraints serve as the final line of defense against data corruption.

Types of Database Constraints

Relational databases support several types of constraints.

Each constraint serves a specific purpose in maintaining database quality.

id

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

posts.user_id

users.id

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    title VARCHAR(200),

    FOREIGN KEY (user_id)
    REFERENCES users(id)
);

user_id = 99

first_name

last_name

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL
);

Muskan@email.com

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    age INTEGER CHECK (age >= 18)
);

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'active'
);

INSERT INTO users(id)
VALUES (1);

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Combining Constraints in Real Applications

In production systems, multiple constraints are usually applied together.

Example: Users Table

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    age INTEGER CHECK(age >= 13),
    status VARCHAR(20) DEFAULT 'active'
);

Applied Constraints

This ensures:

• Every user has a unique identity.
• Names cannot be empty.
• Emails cannot be duplicated.
• Invalid ages cannot be stored.
• Default status is automatically assigned.

Foreign Key Delete Behaviors

When a parent record is deleted, foreign keys determine what happens to dependent child records.

CASCADE

Automatically deletes all related child records.

FOREIGN KEY(user_id)
REFERENCES users(id)
ON DELETE CASCADE

Example:

Deleting a user automatically deletes all posts and comments belonging to that user.

SET NULL

Sets the foreign key value in child records to NULL.

ON DELETE SET NULL

The child record remains, but its relationship is removed.

RESTRICT

Prevents deletion of the parent record if related child records exist.

ON DELETE RESTRICT

Useful when accidental deletion must be avoided.

NO ACTION

Behaves similarly to RESTRICT and is often the default behavior in many databases.

Real-World Example: Blogging Platform

Consider a blogging system containing:

Users
Posts
Comments

Constraints ensure:

• Every user has a unique email address.
• Every post belongs to a valid user.
• Every comment belongs to a valid post.
• Mandatory fields are always provided.
• Invalid data never enters the system.

Without constraints, maintaining data quality becomes extremely difficult as the application scales.

Summary

• Database constraints are rules that enforce data integrity, consistency, and reliability within relational databases.
• PRIMARY KEY uniquely identifies each record and serves as the foundation for table relationships.
• FOREIGN KEY establishes relationships between tables and ensures referential integrity.
• NOT NULL guarantees that mandatory fields always contain valid values.
• UNIQUE prevents duplicate values in columns such as emails, usernames, and phone numbers.
• CHECK validates data against business rules before it is stored in the database.
• DEFAULT automatically assigns predefined values when no explicit value is provided.
• Multiple constraints are often combined to create robust and production-ready database schemas.
• Constraints are a part of system design because they ensure data remains accurate, reliable, and consistent regardless of which application or service interacts with the database.