6. MySQL Constraints

MySQL Constraints

Constraints are rules that MySQL enforces on table data. They help keep data accurate, consistent, and reliable.

Common constraint types include:

Primary key

A primary key uniquely identifies each record in a table.

USE librarydb;

CREATE TABLE authors (
  AuthorID INT PRIMARY KEY,
  FirstName VARCHAR(25),
  LastName VARCHAR(25),
  Email VARCHAR(25)
);

Insert an author:

INSERT INTO authors
VALUES
  (2, "Krish1", "Naik1", "example2@gmail.com");

Inspect the table:

DESC authors;

SELECT *
FROM authors;

Because AuthorID is the primary key, MySQL rejects another row with AuthorID = 2.

Foreign key

A foreign key creates a relationship between tables. Here, each book can reference an author.

CREATE TABLE books (
  BookID INT PRIMARY KEY,
  Title VARCHAR(25) NOT NULL,
  AuthorID INT,
  PublicationYear INT CHECK (PublicationYear > 0),
  FOREIGN KEY (AuthorID) REFERENCES authors(AuthorID)
);

This table enforces several rules:

• BookID must be unique and not null.
• Title cannot be null.
• PublicationYear must be greater than zero.
• AuthorID, when provided, must exist in authors.

Test the foreign key

This insert fails because there is no author with AuthorID = 1.

INSERT INTO books
VALUES
  (1, "Harry Potter", 1, 2022);

This insert succeeds because author 2 exists.

INSERT INTO books
VALUES
  (2, "Harry Potter", 2, 2022);

This insert also succeeds because AuthorID allows NULL. A nullable foreign key means "this relationship is unknown or not assigned yet."

INSERT INTO books
VALUES
  (3, "Harry Potter", NULL, 2022);

Check the rows:

SELECT *
FROM books;

If every book must have an author, define AuthorID INT NOT NULL.

CHECK

CHECK validates that a row satisfies a condition before MySQL stores it. In the books table, this rule prevents invalid publication years:

PublicationYear INT CHECK (PublicationYear > 0)

This insert succeeds because 2022 is greater than zero.

INSERT INTO books
VALUES
  (4, "Clean Code", 2, 2022);

This insert fails because -10 violates the check condition.

INSERT INTO books
VALUES
  (5, "Invalid Book", 2, -10);

Use CHECK when the rule belongs to the data itself, such as positive prices, valid ages, or allowed score ranges.

NOT NULL

NOT NULL requires a value.

CREATE TABLE student (
  id INT NOT NULL,
  first_name VARCHAR(25),
  last_name VARCHAR(25) NOT NULL,
  age INT
);

Add NOT NULL to an existing column:

ALTER TABLE student
MODIFY age INT NOT NULL;

Before making an existing column NOT NULL, make sure no current row contains NULL in that column.

Add and drop a primary key

Add a primary key to an existing table:

ALTER TABLE student
MODIFY id INT NOT NULL PRIMARY KEY;

Drop the primary key:

ALTER TABLE student
DROP PRIMARY KEY;

Dropping a primary key is unusual in production because other tables may depend on it.

UNIQUE

UNIQUE prevents duplicate values in a column.

CREATE TABLE person (
  id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  first_name VARCHAR(25) NOT NULL,
  last_name VARCHAR(25) UNIQUE,
  age INT NOT NULL
);

Insert a person:

INSERT INTO person
VALUES
  (2, "Krish1", "Naik", 32);

If you insert another row with last_name = "Naik", MySQL rejects it because last_name is unique.

SELECT *
FROM person;

DEFAULT

DEFAULT supplies a value when an insert does not provide one. Defaults are useful for status fields, timestamps, counters, and flags.

CREATE TABLE library_members (
  member_id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL,
  status VARCHAR(20) DEFAULT "active",
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This insert provides only the member name. MySQL fills status and created_at.

INSERT INTO library_members (full_name)
VALUES
  ("Aarav Sharma");

Read the row:

SELECT *
FROM library_members;

Expected result: status is "active" and created_at contains the insert timestamp.

You can still override a default by providing an explicit value.

INSERT INTO library_members (full_name, status)
VALUES
  ("Jane Smith", "inactive");

INDEX

An index is a lookup structure that helps MySQL find rows faster. It is especially useful for columns used often in WHERE, JOIN, ORDER BY, and GROUP BY.

Create an index on member status:

CREATE INDEX idx_library_members_status
ON library_members (status);

Check the indexes on a table:

SHOW INDEX
FROM library_members;

Drop the index when it is no longer useful:

DROP INDEX idx_library_members_status
ON library_members;

Indexes are not free. They improve read performance, but they use storage and make writes slightly slower because MySQL must update the index whenever rows change.

Candidate Key

A candidate key is any column, or combination of columns, that can uniquely identify a row. A table can have multiple candidate keys, but only one is selected as the primary key.

In this table, user_id is the primary key. email, phone_number, and username are also candidate keys because each one is unique and required.

CREATE TABLE users (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(100) NOT NULL UNIQUE,
  phone_number VARCHAR(20) NOT NULL UNIQUE,
  username VARCHAR(50) NOT NULL UNIQUE
);

The candidate keys are:

The primary key is the candidate key chosen as the main identifier. Other candidate keys are usually enforced with UNIQUE.

Constraint mindset

Early lessons used tables without constraints so the mechanics were easier to see. Real databases should add constraints as soon as the rules are known. Constraints move data quality checks closer to the data, where they are harder to bypass accidentally.