SQL - MySQL

Module 01 - Database Theory
1. Introduction to Databases2. DBMS Theory Concepts3. Types of Keys4. Database Relationships5. DBMS Interview Questions
Module 02 - CRUD Operations
1. Create - INSERT2. Read - SELECT3. Update - UPDATE4. Delete - DELETE5. Alter - ALTER TABLE
Module 03 - Querying
1. Joins2. Filtering and sorting3. Practice - Filtering and Sort...4. Aggregate functions5. Practice - Aggregate Function...
Module 04 - Data Integrity
1. Constraints in Depth2. Transactions
MySQL Playground
ProfileProfile
Akkal DhamiFull Stack Developer

Building modern web experiences with a focus on performance, scalability, and clean architecture.

© 2026 | Akkal Dhami | All rights reserved

Built with
byAkkal Dhami

Navigation

  • Projects
  • Dev Setup
  • Playbook
  • Templates
  • Networking
  • SQL - MySQL
  • SQL Playground
  • System Design
  • DSA
AKKAL DHAMIAKKAL DHAMIAKKAL DHAMI

Constraints in Depth

Constraints are rules attached to columns that stop invalid data from ever being saved. We've already used NOT NULL, UNIQUE, PRIMARY KEY, and FOREIGN KEY throughout earlier modules.

This lesson covers the ones we've only touched briefly: CHECK, DEFAULT, and what actually happens to related rows when a referenced row is deleted or updated.

CHECK — validating values before they're saved

A CHECK constraint enforces a condition that every row must satisfy.

example.sql
CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    price DECIMAL(6,2),
    published_year INT,
    CHECK (price > 0),
    CHECK (published_year >= 1450 AND published_year <= 2100)
);

Now MySQL rejects invalid inserts before they ever reach the table:

example.sql
-- Rejected — violates the CHECK constraint
INSERT INTO books (title, price, published_year)
VALUES ('Free Book', -5.00, 2020);

You can also name a CHECK constraint, which makes error messages clearer and lets you drop it later by name:

example.sql
ALTER TABLE books
ADD CONSTRAINT chk_positive_price CHECK (price > 0);

DEFAULT — fallback values

Supplies a value automatically when one isn't provided in an INSERT.

example.sql
CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    in_stock BOOLEAN DEFAULT TRUE,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
example.sql
-- in_stock and added_at are filled in automatically
INSERT INTO books (title) VALUES ('Dune');

DEFAULT only applies when a column is omitted entirely — explicitly inserting NULL still results in NULL, not the default.

Referential actions — what happens when a referenced row is deleted

This is the part most beginners miss. By default, deleting a row that other rows depend on (via FOREIGN KEY) simply fails — MySQL protects you from creating orphaned references. But you can define what should happen instead.

example.sql
CREATE TABLE authors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);
 
CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);
ActionWhat happens on DELETE of the parent row
RESTRICT (default)Blocks the delete entirely if any child rows reference it
CASCADEAutomatically deletes all matching child rows too
SET NULLSets the child's foreign key column to NULL instead of deleting the child row
NO ACTIONFunctionally the same as RESTRICT in MySQL

Example — CASCADE in action:

Deleting this author automatically deletes all of their books too

example.sql
DELETE FROM authors WHERE id = 1;

Example — SET NULL instead:

example.sql
CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(id)
        ON DELETE SET NULL
);

Now deleting the author keeps the book, just clears its author_id

example.sql
DELETE FROM authors WHERE id = 1;

Choose carefully. CASCADE is convenient but dangerous if misused — deleting one row can silently wipe out large amounts of related data. SET NULL is often safer for optional relationships (a book without a listed author is fine); CASCADE fits only when the child data has no meaning without the parent (e.g. deleting an order should delete its order line items).

ON UPDATE CASCADE

The same idea applies to updates — if a parent's primary key value ever changes, ON UPDATE CASCADE automatically updates every child row's foreign key to match:

example.sql
-- If authors.id ever changed (rare, since it's usually AUTO_INCREMENT),
-- every books.author_id referencing it would update automatically

In practice this matters more when using natural keys (like an email or code) as a reference, since those are more likely to change than a surrogate id.

Combining constraints

A single column can carry several constraints at once:

example.sql
CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    isbn VARCHAR(13) NOT NULL UNIQUE,
    price DECIMAL(6,2) NOT NULL CHECK (price > 0),
    in_stock BOOLEAN DEFAULT TRUE
);

Quick Reference

ConstraintEnforces
CHECK (condition)Value must satisfy a condition before it's saved
DEFAULT valueFallback value used when none is provided
ON DELETE RESTRICTBlocks deleting a parent row with existing children
ON DELETE CASCADEDeletes child rows automatically along with the parent
ON DELETE SET NULLClears the child's foreign key instead of deleting it
ON UPDATE CASCADEUpdates child foreign keys automatically if the parent key changes
Practice - Aggregate Functions
Transactions