SQL Constraints - Enforcing Rules at the Table Level

Introduction

In any real-world system — be it a school database or a bank — rules matter. Students must have unique roll numbers, marks must be within a valid range, and names can’t be empty. These rules are enforced using SQL Constraints.

What are SQL Constraints?

SQL constraints are rules applied to table columns to ensure data integrity. They prevent invalid, duplicate, or inconsistent data from being entered into the database.

Common SQL Constraints

• PRIMARY KEY
• UNIQUE
• NOT NULL
• CHECK
• DEFAULT
• FOREIGN KEY

Sample Table – students

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  class VARCHAR(10) DEFAULT 'Not Assigned',
  age INT CHECK (age >= 5 AND age <= 18),
  city VARCHAR(30)
);

1. PRIMARY KEY

Ensures that each row has a unique identifier. Cannot be NULL.

-- roll_no is PRIMARY KEY
INSERT INTO students VALUES (1, 'Aarav Sharma', '10A', 15, 'Delhi');

-- This will fail (duplicate roll_no)
INSERT INTO students VALUES (1, 'Diya Iyer', '9B', 14, 'Chennai');

2. NOT NULL

Prevents NULL values in a column.

-- This will fail (name is NOT NULL)
INSERT INTO students (roll_no, age) VALUES (2, 14);

3. DEFAULT

Sets a default value if none is provided during insertion.

INSERT INTO students (roll_no, name, age, city)
VALUES (3, 'Sneha Patil', 13, 'Pune');

roll_no | name           | class         | age | city
--------+----------------+---------------+-----+-------
3       | Sneha Patil    | Not Assigned  | 13  | Pune

4. CHECK

Limits the values in a column to meet a specific condition.

-- This will fail (age < 5)
INSERT INTO students VALUES (4, 'Mehul Agarwal', '8C', 4, 'Delhi');

5. UNIQUE

Ensures all values in a column are different.

ALTER TABLE students
ADD CONSTRAINT unique_city UNIQUE(city);

Now, inserting a student with a duplicate city will fail:

-- This will fail if 'Delhi' is already used
INSERT INTO students VALUES (5, 'Saira Bano', '10A', 15, 'Delhi');

6. FOREIGN KEY

Enforces a relationship between two tables.

Table: results

CREATE TABLE results (
  id INT PRIMARY KEY,
  roll_no INT,
  subject VARCHAR(30),
  marks INT,
  FOREIGN KEY (roll_no) REFERENCES students(roll_no)
);

This ensures that results.roll_no must match an existing student.

-- Works if roll_no 1 exists in students
INSERT INTO results VALUES (1, 1, 'Maths', 85);

-- Fails if roll_no 99 doesn’t exist
INSERT INTO results VALUES (2, 99, 'Science', 90);

Best Practices

• Always define PRIMARY KEY for every table.
• Use NOT NULL for mandatory fields like names.
• Use CHECK to enforce logical limits (e.g., age, marks).
• Use FOREIGN KEY to maintain relationships between tables.

Real-World School Example

In a school database, constraints help prevent invalid data like:

• Duplicate roll numbers (PRIMARY KEY)
• Missing student names (NOT NULL)
• Marks outside valid ranges (CHECK)
• Results for students not in the system (FOREIGN KEY)

Summary

SQL constraints are essential for creating trustworthy, rule-bound databases. They guard against bad data, help maintain relationships, and ensure that your database reflects real-world logic and limitations. Like discipline in a school — constraints keep everything in order.

What’s Next?

Coming up next: SQL Transactions — how to execute multiple queries safely with rollback support when something fails.