SQL Constraints | Academic Block

SQL CONSTRAINTS

Learn how SQL constraints enforce rules on table data and help maintain accuracy, consistency, and integrity in a database.

What are SQL Constraints?

SQL constraints are rules applied to table columns to control the type of data that can be stored. They help prevent invalid, duplicate, or inconsistent data from entering a database.

Common SQL constraints include NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT.

NOT NULL Constraint

The NOT NULL constraint prevents a column from storing NULL values. It is useful when a value is required for every row.

DROP TABLE IF EXISTS students;

CREATE TABLE students (
  student_id INTEGER PRIMARY KEY,
  student_name TEXT NOT NULL,
  course TEXT NOT NULL
);

INSERT INTO students (student_name, course) VALUES
  ('Aarav', 'Physics'),
  ('Meera', 'Computer Science'),
  ('Kabir', 'Mathematics');

SELECT *
FROM students
ORDER BY student_id;

UNIQUE Constraint

The UNIQUE constraint ensures that values in a column are not duplicated. This is useful for values such as email addresses, usernames, or registration numbers.

DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL,
  email TEXT UNIQUE
);

INSERT INTO customers (customer_name, email) VALUES
  ('Riya', 'riya@example.com'),
  ('Arjun', 'arjun@example.com'),
  ('Nisha', 'nisha@example.com');

SELECT *
FROM customers
ORDER BY customer_id;

PRIMARY KEY Constraint

A PRIMARY KEY uniquely identifies each row in a table. A primary key cannot contain NULL values and its values must be unique.

DROP TABLE IF EXISTS products;

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  price REAL
);

INSERT INTO products (product_id, product_name, price) VALUES
  (101, 'Wireless Mouse', 799.00),
  (102, 'USB Hub', 599.00),
  (103, 'Laptop Stand', 1499.00);

SELECT *
FROM products
ORDER BY product_id;

FOREIGN KEY Constraint

A FOREIGN KEY establishes a relationship between two tables. It ensures that a value in one table refers to an existing value in another table.

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  product_name TEXT NOT NULL,
  FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
);

INSERT INTO customers VALUES
  (1, 'Aarav'),
  (2, 'Meera'),
  (3, 'Kabir');

INSERT INTO orders VALUES
  (101, 1, 'Keyboard'),
  (102, 2, 'Monitor'),
  (103, 1, 'Mouse');

SELECT
  orders.order_id,
  customers.customer_name,
  orders.product_name
FROM orders
JOIN customers
  ON orders.customer_id = customers.customer_id
ORDER BY orders.order_id;

CHECK Constraint

The CHECK constraint requires values to satisfy a specified condition before they can be inserted or updated.

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
  employee_id INTEGER PRIMARY KEY,
  employee_name TEXT NOT NULL,
  age INTEGER CHECK (age >= 18),
  salary REAL CHECK (salary > 0)
);

INSERT INTO employees (employee_name, age, salary) VALUES
  ('Neha', 28, 52000),
  ('Vikram', 34, 68000),
  ('Sana', 25, 47000);

SELECT *
FROM employees
ORDER BY employee_id;

DEFAULT Constraint

The DEFAULT constraint automatically provides a value when an INSERT statement does not specify a value for that column.

DROP TABLE IF EXISTS tasks;

CREATE TABLE tasks (
  task_id INTEGER PRIMARY KEY,
  task_name TEXT NOT NULL,
  status TEXT DEFAULT 'Pending'
);

INSERT INTO tasks (task_name) VALUES
  ('Design homepage'),
  ('Test login form'),
  ('Update documentation');

SELECT *
FROM tasks
ORDER BY task_id;

Using Multiple Constraints

Multiple constraints can be applied to the same table. This allows you to enforce several data rules at the same time.

DROP TABLE IF EXISTS courses;

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  course_code TEXT NOT NULL UNIQUE,
  course_name TEXT NOT NULL,
  duration_months INTEGER CHECK (duration_months > 0),
  status TEXT DEFAULT 'Active'
);

INSERT INTO courses (
  course_code,
  course_name,
  duration_months
) VALUES
  ('PHY101', 'Applied Physics', 6),
  ('DES201', 'Product Design', 8),
  ('ROB301', 'Robotics Engineering', 12);

SELECT *
FROM courses
ORDER BY course_id;

Column Constraints vs Table Constraints

A constraint can be written directly beside a column definition or separately at the table level. Table-level constraints are particularly useful when a rule involves multiple columns.

DROP TABLE IF EXISTS enrollments;

CREATE TABLE enrollments (
  enrollment_id INTEGER PRIMARY KEY,
  student_name TEXT NOT NULL,
  course_name TEXT NOT NULL,
  score INTEGER,
  UNIQUE (student_name, course_name),
  CHECK (score BETWEEN 0 AND 100)
);

INSERT INTO enrollments (
  student_name,
  course_name,
  score
) VALUES
  ('Aarav', 'Physics', 88),
  ('Meera', 'Mathematics', 94),
  ('Kabir', 'Chemistry', 81);

SELECT *
FROM enrollments
ORDER BY enrollment_id;

Common SQL Constraints

Constraint	Purpose	Example
NOT NULL	Prevents NULL values	name TEXT NOT NULL
UNIQUE	Prevents duplicate values	email TEXT UNIQUE
PRIMARY KEY	Uniquely identifies rows	id INTEGER PRIMARY KEY
FOREIGN KEY	Links related tables	FOREIGN KEY (customer_id)
CHECK	Requires a condition to be true	age INTEGER CHECK (age >= 18)
DEFAULT	Provides a default value	status TEXT DEFAULT ‘Active’

Constraints and Data Integrity

Constraints help maintain data integrity by preventing invalid data from being stored. For example, NOT NULL ensures required information is present, UNIQUE prevents unwanted duplicates, and FOREIGN KEY helps maintain relationships between tables.

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS registrations;
DROP TABLE IF EXISTS workshops;

CREATE TABLE workshops (
  workshop_id INTEGER PRIMARY KEY,
  workshop_name TEXT NOT NULL,
  seats INTEGER CHECK (seats > 0)
);

CREATE TABLE registrations (
  registration_id INTEGER PRIMARY KEY,
  workshop_id INTEGER NOT NULL,
  participant_email TEXT NOT NULL UNIQUE,
  status TEXT DEFAULT 'Confirmed',
  FOREIGN KEY (workshop_id)
    REFERENCES workshops(workshop_id)
);

INSERT INTO workshops VALUES
  (1, 'Robotics Lab', 20),
  (2, 'CAD Workshop', 15);

INSERT INTO registrations (
  workshop_id,
  participant_email
) VALUES
  (1, 'student1@example.com'),
  (2, 'student2@example.com');

SELECT
  registrations.registration_id,
  workshops.workshop_name,
  registrations.participant_email,
  registrations.status
FROM registrations
JOIN workshops
  ON registrations.workshop_id = workshops.workshop_id
ORDER BY registrations.registration_id;

Important Points

Constraints define rules for valid table data.
NOT NULL prevents missing values.
UNIQUE prevents duplicate values.
PRIMARY KEY uniquely identifies each row.
FOREIGN KEY maintains relationships between tables.
CHECK validates values against a condition.
DEFAULT supplies a value when none is provided.
Multiple constraints can be applied to the same table or column.

Best Practices

Use PRIMARY KEY for columns that uniquely identify records.
Use NOT NULL for information that every row must contain.
Use UNIQUE when duplicate values should not be allowed.
Use CHECK to enforce valid ranges or conditions.
Use FOREIGN KEY to maintain relationships between tables.
Choose sensible DEFAULT values for optional columns.
Design constraints carefully before inserting large amounts of data.

SQL CONSTRAINTS help protect the integrity of your database by enforcing rules on the data. Using constraints correctly makes tables more reliable, consistent, and easier to maintain.

Ctrl + Enter to run SQL code Esc to close editor

🧪 Test Your SQL Code

Edit the SQL code on the left and click “Run Code” to see the result on the right.

📝 SQL Code

👁️ Preview (query result)

Click “Run Code” to see the result here.