Constraints in SQL

Constraints are rules applied to table columns that restrict the type of data that can be inserted or updated. They enforce data integrity at the database level — indepen

What are Constraints?

Constraints are rules applied to table columns that restrict the type of data that can be inserted or updated. They enforce data integrity at the database level — independent of the application.

Constraints are important because application code can change, fail, or be bypassed, but database constraints remain close to the data. If a salary must always be positive, a department id must exist before an employee can reference it, or an email must be unique, these rules should be enforced inside the database. This makes data reliable even when multiple applications or users access the same database.

Related topics: DDL Commands, DML Commands, Keys in DBMS, and Relational Model.

Why Constraints are Needed

Without constraints, invalid data can enter tables very easily. A student table may contain two students with the same roll number, an order may point to a customer that does not exist, or a product price may become negative. Constraints prevent these problems automatically and help maintain entity integrity, referential integrity, and domain integrity.

Types of Constraints

NOT NULL	Column must contain a value
UNIQUE	No duplicate values
PRIMARY KEY	Unique row identifier
FOREIGN KEY	References another table
CHECK	Validates a condition
DEFAULT	Supplies a default value

1. NOT NULL

Ensures that a column cannot contain NULL values. Every row must have a value for this column.

sql exampleWoHoTech

CREATE TABLE Employee (
    emp_id   INT         NOT NULL,
    name     VARCHAR(100) NOT NULL,   -- name is mandatory
    email    VARCHAR(100),            -- email is optional (can be NULL)
    salary   DECIMAL(10,2) NOT NULL
);

-- Adding NOT NULL to existing column
ALTER TABLE Employee MODIFY COLUMN email VARCHAR(100) NOT NULL;

2. UNIQUE

Ensures all values in a column (or combination of columns) are distinct across all rows. Unlike PRIMARY KEY, a table can have multiple UNIQUE constraints, and UNIQUE columns can contain NULL.

sql exampleWoHoTech

CREATE TABLE Employee (
    emp_id  INT         PRIMARY KEY,
    email   VARCHAR(100) UNIQUE,         -- Column-level
    aadhaar VARCHAR(12),
    phone   VARCHAR(15),
    CONSTRAINT uq_aadhaar UNIQUE (aadhaar),     -- Table-level with name
    CONSTRAINT uq_phone   UNIQUE (phone)
);

-- Composite unique constraint
CREATE TABLE Enrollment (
    student_id INT,
    course_id  INT,
    CONSTRAINT uq_enrollment UNIQUE (student_id, course_id)
);

3. PRIMARY KEY

Uniquely identifies each row. Combines NOT NULL + UNIQUE. Only one primary key per table.

sql exampleWoHoTech

-- Simple primary key (column-level)
CREATE TABLE Department (
    dept_id   INT         PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL
);

-- Composite primary key (table-level)
CREATE TABLE OrderItem (
    order_id   INT,
    product_id INT,
    quantity   INT NOT NULL,
    price      DECIMAL(10, 2),
    PRIMARY KEY (order_id, product_id)
);

-- Auto-increment primary key
CREATE TABLE Student (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name       VARCHAR(100) NOT NULL
);

4. FOREIGN KEY

Enforces referential integrity between two tables. The value in the referencing column must exist in the referenced column (or be NULL).

sql exampleWoHoTech

CREATE TABLE Employee (
    emp_id   INT PRIMARY KEY,
    name     VARCHAR(100),
    dept_id  INT,
    mgr_id   INT,

    -- Simple foreign key
    CONSTRAINT fk_dept FOREIGN KEY (dept_id)
        REFERENCES Department(dept_id),

    -- Self-referencing foreign key
    CONSTRAINT fk_manager FOREIGN KEY (mgr_id)
        REFERENCES Employee(emp_id)
);

Referential Actions

sql exampleWoHoTech

CREATE TABLE Employee (
    emp_id  INT PRIMARY KEY,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
        ON DELETE CASCADE      -- Delete employee when dept is deleted
        ON UPDATE CASCADE      -- Update dept_id when department's PK changes
);

-- Options:
-- ON DELETE / ON UPDATE:
--   CASCADE     → Propagate change to child rows
--   SET NULL    → Set FK to NULL in child rows
--   SET DEFAULT → Set FK to its default value
--   RESTRICT    → Prevent the parent change (error)
--   NO ACTION   → Same as RESTRICT in most DBs

5. CHECK

Ensures that all values in a column satisfy a specific condition. Custom business rule enforcement.

sql exampleWoHoTech

CREATE TABLE Employee (
    emp_id  INT          PRIMARY KEY,
    name    VARCHAR(100) NOT NULL,
    salary  DECIMAL(10,2) CHECK (salary > 0),
    age     INT           CHECK (age BETWEEN 18 AND 65),
    gender  CHAR(1)       CHECK (gender IN ('M', 'F', 'O')),
    email   VARCHAR(100)  CHECK (email LIKE '%@%.%')
);

-- Named CHECK constraint
CREATE TABLE Product (
    product_id  INT PRIMARY KEY,
    price       DECIMAL(10, 2),
    discount    DECIMAL(5, 2),
    CONSTRAINT chk_price    CHECK (price > 0),
    CONSTRAINT chk_discount CHECK (discount BETWEEN 0 AND 100),
    CONSTRAINT chk_valid    CHECK (price > discount)  -- cross-column rule
);

6. DEFAULT

Provides a default value for a column when no value is specified during INSERT.

sql exampleWoHoTech

CREATE TABLE Employee (
    emp_id    INT          PRIMARY KEY,
    name      VARCHAR(100) NOT NULL,
    status    VARCHAR(20)  DEFAULT 'Active',
    hire_date DATE         DEFAULT (CURRENT_DATE),
    salary    DECIMAL(10,2) DEFAULT 0.00,
    dept_id   INT          DEFAULT 1
);

-- Usage: no value for status, hire_date, salary
INSERT INTO Employee (emp_id, name) VALUES (101, 'Alice');
-- status = 'Active', hire_date = today, salary = 0.00, dept_id = 1

Constraint Operations

Adding Constraints After Table Creation

sql exampleWoHoTech

-- Add NOT NULL (via MODIFY)
ALTER TABLE Employee MODIFY COLUMN email VARCHAR(100) NOT NULL;

-- Add UNIQUE
ALTER TABLE Employee ADD CONSTRAINT uq_email UNIQUE (email);

-- Add CHECK
ALTER TABLE Employee ADD CONSTRAINT chk_salary CHECK (salary > 0);

-- Add FOREIGN KEY
ALTER TABLE Employee ADD CONSTRAINT fk_dept
    FOREIGN KEY (dept_id) REFERENCES Department(dept_id);

Dropping Constraints

sql exampleWoHoTech

ALTER TABLE Employee DROP CONSTRAINT uq_email;
ALTER TABLE Employee DROP CONSTRAINT chk_salary;
ALTER TABLE Employee DROP FOREIGN KEY fk_dept;
ALTER TABLE Employee DROP PRIMARY KEY;

Constraint Summary Table

Constraint	NULL Allowed	Multiple per Table	Purpose
NOT NULL	No	Yes	Mandatory values
UNIQUE	Yes (one NULL)	Yes	No duplicates
PRIMARY KEY	No	Exactly 1	Row identifier
FOREIGN KEY	Yes	Yes	Referential integrity
CHECK	Yes	Yes	Business rule validation
DEFAULT	—	Yes	Default value if omitted

Common Constraint Mistakes

Confusing UNIQUE with PRIMARY KEY. A table can have many unique constraints, but only one primary key.
Forgetting that a foreign key must reference a candidate key or primary key in the parent table.
Adding a NOT NULL constraint to an existing column that already contains NULL values.
Assuming CHECK works identically in every DBMS version. Older MySQL versions parsed CHECK but did not enforce it.
Dropping parent rows without understanding ON DELETE CASCADE, SET NULL, or RESTRICT.

Exam and Interview Notes

For exams, remember that constraints are normally defined during CREATE TABLE or later using ALTER TABLE. For interviews, be ready to explain how constraints protect data integrity and how primary key, foreign key, unique key, and check constraints differ from each other.

What are Constraints?

Related topics: DDL Commands, DML Commands, Keys in DBMS, and Relational Model.

Why Constraints are Needed

Types of Constraints

NOT NULL	Column must contain a value
UNIQUE	No duplicate values
PRIMARY KEY	Unique row identifier
FOREIGN KEY	References another table
CHECK	Validates a condition
DEFAULT	Supplies a default value

1. NOT NULL

Ensures that a column cannot contain NULL values. Every row must have a value for this column.

sql exampleWoHoTech

CREATE TABLE Employee (
    emp_id   INT         NOT NULL,
    name     VARCHAR(100) NOT NULL,   -- name is mandatory
    email    VARCHAR(100),            -- email is optional (can be NULL)
    salary   DECIMAL(10,2) NOT NULL
);

-- Adding NOT NULL to existing column
ALTER TABLE Employee MODIFY COLUMN email VARCHAR(100) NOT NULL;

2. UNIQUE

Ensures all values in a column (or combination of columns) are distinct across all rows. Unlike PRIMARY KEY, a table can have multiple UNIQUE constraints, and UNIQUE columns can contain NULL.

sql exampleWoHoTech

CREATE TABLE Employee (
    emp_id  INT         PRIMARY KEY,
    email   VARCHAR(100) UNIQUE,         -- Column-level
    aadhaar VARCHAR(12),
    phone   VARCHAR(15),
    CONSTRAINT uq_aadhaar UNIQUE (aadhaar),     -- Table-level with name
    CONSTRAINT uq_phone   UNIQUE (phone)
);

-- Composite unique constraint
CREATE TABLE Enrollment (
    student_id INT,
    course_id  INT,
    CONSTRAINT uq_enrollment UNIQUE (student_id, course_id)
);

3. PRIMARY KEY

Uniquely identifies each row. Combines NOT NULL + UNIQUE. Only one primary key per table.

sql exampleWoHoTech

-- Simple primary key (column-level)
CREATE TABLE Department (
    dept_id   INT         PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL
);

-- Composite primary key (table-level)
CREATE TABLE OrderItem (
    order_id   INT,
    product_id INT,
    quantity   INT NOT NULL,
    price      DECIMAL(10, 2),
    PRIMARY KEY (order_id, product_id)
);

-- Auto-increment primary key
CREATE TABLE Student (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name       VARCHAR(100) NOT NULL
);

4. FOREIGN KEY

Enforces referential integrity between two tables. The value in the referencing column must exist in the referenced column (or be NULL).

sql exampleWoHoTech

CREATE TABLE Employee (
    emp_id   INT PRIMARY KEY,
    name     VARCHAR(100),
    dept_id  INT,
    mgr_id   INT,

    -- Simple foreign key
    CONSTRAINT fk_dept FOREIGN KEY (dept_id)
        REFERENCES Department(dept_id),

    -- Self-referencing foreign key
    CONSTRAINT fk_manager FOREIGN KEY (mgr_id)
        REFERENCES Employee(emp_id)
);

Referential Actions

sql exampleWoHoTech

CREATE TABLE Employee (
    emp_id  INT PRIMARY KEY,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
        ON DELETE CASCADE      -- Delete employee when dept is deleted
        ON UPDATE CASCADE      -- Update dept_id when department's PK changes
);

-- Options:
-- ON DELETE / ON UPDATE:
--   CASCADE     → Propagate change to child rows
--   SET NULL    → Set FK to NULL in child rows
--   SET DEFAULT → Set FK to its default value
--   RESTRICT    → Prevent the parent change (error)
--   NO ACTION   → Same as RESTRICT in most DBs

5. CHECK

Ensures that all values in a column satisfy a specific condition. Custom business rule enforcement.

sql exampleWoHoTech

CREATE TABLE Employee (
    emp_id  INT          PRIMARY KEY,
    name    VARCHAR(100) NOT NULL,
    salary  DECIMAL(10,2) CHECK (salary > 0),
    age     INT           CHECK (age BETWEEN 18 AND 65),
    gender  CHAR(1)       CHECK (gender IN ('M', 'F', 'O')),
    email   VARCHAR(100)  CHECK (email LIKE '%@%.%')
);

-- Named CHECK constraint
CREATE TABLE Product (
    product_id  INT PRIMARY KEY,
    price       DECIMAL(10, 2),
    discount    DECIMAL(5, 2),
    CONSTRAINT chk_price    CHECK (price > 0),
    CONSTRAINT chk_discount CHECK (discount BETWEEN 0 AND 100),
    CONSTRAINT chk_valid    CHECK (price > discount)  -- cross-column rule
);

6. DEFAULT

Provides a default value for a column when no value is specified during INSERT.

sql exampleWoHoTech

CREATE TABLE Employee (
    emp_id    INT          PRIMARY KEY,
    name      VARCHAR(100) NOT NULL,
    status    VARCHAR(20)  DEFAULT 'Active',
    hire_date DATE         DEFAULT (CURRENT_DATE),
    salary    DECIMAL(10,2) DEFAULT 0.00,
    dept_id   INT          DEFAULT 1
);

-- Usage: no value for status, hire_date, salary
INSERT INTO Employee (emp_id, name) VALUES (101, 'Alice');
-- status = 'Active', hire_date = today, salary = 0.00, dept_id = 1

Constraint Operations

Adding Constraints After Table Creation

sql exampleWoHoTech

-- Add NOT NULL (via MODIFY)
ALTER TABLE Employee MODIFY COLUMN email VARCHAR(100) NOT NULL;

-- Add UNIQUE
ALTER TABLE Employee ADD CONSTRAINT uq_email UNIQUE (email);

-- Add CHECK
ALTER TABLE Employee ADD CONSTRAINT chk_salary CHECK (salary > 0);

-- Add FOREIGN KEY
ALTER TABLE Employee ADD CONSTRAINT fk_dept
    FOREIGN KEY (dept_id) REFERENCES Department(dept_id);

Dropping Constraints

sql exampleWoHoTech

ALTER TABLE Employee DROP CONSTRAINT uq_email;
ALTER TABLE Employee DROP CONSTRAINT chk_salary;
ALTER TABLE Employee DROP FOREIGN KEY fk_dept;
ALTER TABLE Employee DROP PRIMARY KEY;

Constraint Summary Table

Constraint	NULL Allowed	Multiple per Table	Purpose
NOT NULL	No	Yes	Mandatory values
UNIQUE	Yes (one NULL)	Yes	No duplicates
PRIMARY KEY	No	Exactly 1	Row identifier
FOREIGN KEY	Yes	Yes	Referential integrity
CHECK	Yes	Yes	Business rule validation
DEFAULT	—	Yes	Default value if omitted

Common Constraint Mistakes

Confusing UNIQUE with PRIMARY KEY. A table can have many unique constraints, but only one primary key.
Forgetting that a foreign key must reference a candidate key or primary key in the parent table.
Adding a NOT NULL constraint to an existing column that already contains NULL values.
Assuming CHECK works identically in every DBMS version. Older MySQL versions parsed CHECK but did not enforce it.
Dropping parent rows without understanding ON DELETE CASCADE, SET NULL, or RESTRICT.

Constraints in SQL

What are Constraints?

Why Constraints are Needed

Types of Constraints

1. NOT NULL

2. UNIQUE

3. PRIMARY KEY

4. FOREIGN KEY

Referential Actions

5. CHECK

6. DEFAULT

Constraint Operations

Adding Constraints After Table Creation

Dropping Constraints

Constraint Summary Table

Common Constraint Mistakes

Exam and Interview Notes

Continue learning this concept

Constraints in SQL

What are Constraints?

Why Constraints are Needed

Types of Constraints

1. NOT NULL

2. UNIQUE

3. PRIMARY KEY

4. FOREIGN KEY

Referential Actions

5. CHECK

6. DEFAULT

Constraint Operations

Adding Constraints After Table Creation

Dropping Constraints

Constraint Summary Table

Common Constraint Mistakes

Exam and Interview Notes

Continue learning this concept