SQL Constraints

Constraints are rules applied to columns in a table to control what data is allowed. They ensure that only valid, accurate, and consistent data enters the database. If an operation violates a constraint, the database rejects it with an error.

Think of constraints like rules on a form: some fields are mandatory, some values must be unique, and some fields must contain only specific types of values. Constraints enforce these rules automatically at the database level.

Types of SQL Constraints

1. NOT NULL Constraint

A column with NOT NULL will not accept blank (NULL) values. A value must always be provided when inserting or updating a record for this column.

CREATE TABLE Students (
    StudentID   INT,
    StudentName VARCHAR(50) NOT NULL,
    Age         INT NOT NULL
);

If an INSERT is attempted without a StudentName or Age, the database will return an error.

-- This will cause an error (StudentName is NOT NULL)
INSERT INTO Students (StudentID, Age) VALUES (1, 20);

2. UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are different across all rows. It allows NULL values (unless also marked NOT NULL).

CREATE TABLE Students (
    StudentID INT,
    Email     VARCHAR(100) UNIQUE
);

-- First insert - OK
INSERT INTO Students VALUES (1, 'ravi@gmail.com');

-- Second insert - ERROR: Duplicate entry
INSERT INTO Students VALUES (2, 'ravi@gmail.com');

3. PRIMARY KEY Constraint

A PRIMARY KEY uniquely identifies every row in a table. It enforces both NOT NULL and UNIQUE together. Each table can have only one primary key, but it can be made up of multiple columns (called a composite primary key).

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

It is common to combine PRIMARY KEY with AUTO_INCREMENT:

CREATE TABLE Students (
    StudentID   INT AUTO_INCREMENT PRIMARY KEY,
    StudentName VARCHAR(50) NOT NULL
);

Composite Primary Key (Multiple Columns)

CREATE TABLE Enrollments (
    StudentID  INT,
    CourseID   INT,
    EnrollDate DATE,
    PRIMARY KEY (StudentID, CourseID)
);

Here, the combination of StudentID and CourseID is unique — meaning a student can enroll in the same course only once, but can enroll in multiple different courses.

4. FOREIGN KEY Constraint

A FOREIGN KEY links a column in one table to the PRIMARY KEY of another table. It enforces referential integrity — ensuring that a value in the foreign key column must exist in the referenced table.

CREATE TABLE Courses (
    CourseID   INT PRIMARY KEY,
    CourseName VARCHAR(50) NOT NULL
);

CREATE TABLE Enrollments (
    EnrollID  INT AUTO_INCREMENT PRIMARY KEY,
    StudentID INT,
    CourseID  INT,
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

Effect:

• A row in Enrollments can only use a CourseID that already exists in the Courses table.
• A course cannot be deleted from the Courses table if students are enrolled in it (by default).

-- This will FAIL if CourseID 99 does not exist in the Courses table
INSERT INTO Enrollments (StudentID, CourseID) VALUES (1, 99);

ON DELETE and ON UPDATE Actions for Foreign Keys

FOREIGN KEY (CourseID) REFERENCES Courses(CourseID) ON DELETE CASCADE

5. CHECK Constraint

The CHECK constraint validates that values in a column satisfy a specific condition before they are accepted.

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(50) NOT NULL,
    Age INT CHECK (Age >= 16 AND Age <= 60),
    Fees DECIMAL(8,2) CHECK (Fees > 0)
);

-- This will ERROR because Age = 12 fails the CHECK constraint
INSERT INTO Students VALUES (1, 'Ravi', 12, 45000);

6. DEFAULT Constraint

The DEFAULT constraint provides an automatic value for a column when no value is specified during an INSERT.

CREATE TABLE Students (
    StudentID   INT AUTO_INCREMENT PRIMARY KEY,
    StudentName VARCHAR(50) NOT NULL,
    City        VARCHAR(30) DEFAULT 'Unknown',
    JoinDate    DATE        DEFAULT (CURRENT_DATE)
);

-- City and JoinDate will use their defaults
INSERT INTO Students (StudentName) VALUES ('Ravi');

After this insert, Ravi's City will be 'Unknown' and JoinDate will be today's date.

Adding Constraints to an Existing Table

Constraints can be added to an existing table using the ALTER TABLE command:

-- Add a NOT NULL constraint
ALTER TABLE Students MODIFY Age INT NOT NULL;

-- Add a UNIQUE constraint
ALTER TABLE Students ADD CONSTRAINT uq_email UNIQUE (Email);

-- Add a CHECK constraint
ALTER TABLE Students ADD CONSTRAINT chk_age CHECK (Age >= 16);

-- Add a FOREIGN KEY
ALTER TABLE Enrollments
ADD CONSTRAINT fk_course
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID);

Key Points to Remember

• Constraints enforce data quality rules at the database level — they protect the data automatically.
• PRIMARY KEY = NOT NULL + UNIQUE. Every table should have one.
• FOREIGN KEY creates a relationship between two tables and ensures referential integrity.
• CHECK validates data against a custom condition during insertion or update.
• DEFAULT provides a fallback value when no value is supplied.

Summary

Constraints are rules that control what data can be stored in a database table. They prevent invalid, duplicate, or inconsistent data from being inserted. The six main constraints — NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT — together ensure that the database always contains clean, accurate, and meaningful data. Constraints are one of the most important aspects of proper database design.