← Back to Chapters

MySQL Constraints

? MySQL Constraints

MySQL Table Design Data Integrity

⚡ Quick Overview

In MySQL, constraints are rules applied to columns or tables to control what type of data can be stored. They help maintain accuracy, consistency, and reliability of data in your database.

Common constraints include:

  • NOT NULL – a column must always have a value.
  • UNIQUE – all values in the column must be different.
  • PRIMARY KEY – uniquely identifies each record in the table.
  • FOREIGN KEY – links rows between two related tables.

? Key Concepts

  • NOT NULL
    Ensures a column cannot store NULL values. The user must always provide a value.
  • UNIQUE
    No two rows in that column can have the same value (e.g., email, username).
  • PRIMARY KEY
    A combination of NOT NULL and UNIQUE. Each table should normally have one primary key.
  • FOREIGN KEY
    Creates a relationship between two tables and enforces referential integrity.

? Syntax & Theory

You can add constraints in two ways:

  1. Column-level constraints – defined right after the column definition.
  2. Table-level constraints – defined at the end of all column definitions.

Example ideas:

  • Make ID the PRIMARY KEY.
  • Make Name NOT NULL so it must always be provided.
  • Make Email UNIQUE so no duplicate email is allowed.
  • Use a FOREIGN KEY on DepartmentID to link to a Departments table.

? Example: CREATE TABLE with Constraints

? View Code Example 
-- Create Employees table with common constraints
CREATE TABLE Employees (
ID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Age INT,
Email VARCHAR(100) UNIQUE,
DepartmentID INT,
FOREIGN KEY (DepartmentID) REFERENCES Departments(ID)
);

? Adding Constraints Using ALTER TABLE

? View Code Example 
-- Add a foreign key to an existing table
ALTER TABLE Employees
ADD CONSTRAINT fk_employees_department
FOREIGN KEY (DepartmentID)
REFERENCES Departments(ID);

? Live Behavior / Explanation

  • If you try to insert a row into Employees without a Name, MySQL will throw an error because of the NOT NULL constraint.
  • If you try to insert another row with the same Email, the UNIQUE constraint will prevent it.
  • The PRIMARY KEY on ID ensures each employee has a unique identifier.
  • The FOREIGN KEY on DepartmentID ensures that any department assigned to an employee must already exist in the Departments table.

✅ Tips & Best Practices

  • Always define a PRIMARY KEY for every table to uniquely identify rows.
  • Use NOT NULL on columns that should always have a value (e.g., names, important IDs).
  • Use UNIQUE for columns like Email, Username, or Phone to avoid duplicates.
  • Define FOREIGN KEY relationships to maintain data integrity across related tables.
  • Pick clear and consistent names for constraints, especially when using ALTER TABLE.

? Try It Yourself

  • Create a table Students with constraints:
    • StudentID – INT, PRIMARY KEY
    • Name – VARCHAR(100), NOT NULL
    • Email – VARCHAR(100), UNIQUE
  • Create a table Courses and link it with Students using a FOREIGN KEY.
  • Use ALTER TABLE to add a new UNIQUE constraint to an existing column in any table.
  • Try inserting duplicate or invalid data and observe which constraint stops the insert.