Skip to content

Table Constraints

Enforce data integrity with UNIQUE, NOT NULL, DEFAULT, CHECK, and FOREIGN KEY.

Your school database has students and enrollments from Stage 7. But right now, nothing stops bad data: two students could share the same email, or an enrollment could reference a student that does not exist. Constraints are rules the database enforces automatically. They prevent bad data from entering in the first place.

Definition

Table constraints enforce data integrity rules at the database level. UNIQUE ensures all values in a column are different, NOT NULL prevents missing values, DEFAULT provides a fallback value, CHECK validates custom rules, and FOREIGN KEY links to another table's primary key.

Start fresh: Click Reset in the sidebar to clear your database, then run the setup below.

CREATE DATABASE IF NOT EXISTS school;
USE school;

CREATE TABLE IF NOT EXISTS students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS enrollments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  course_name VARCHAR(100) NOT NULL,
  score DECIMAL(5,2),
  FOREIGN KEY (student_id) REFERENCES students(id)
);

INSERT INTO students (name, email) VALUES
  ('Alice', '[email protected]'),
  ('Bob', '[email protected]'),
  ('Charlie', '[email protected]');

INSERT INTO enrollments (student_id, course_name, score) VALUES
  (1, 'Math', 90), (1, 'Science', 85),
  (2, 'Math', 78), (2, 'Science', NULL),
  (3, 'Math', 92);

NOT NULL

NOT NULL prevents missing values. If you try to insert a row without a required column, MySQL rejects it. Right now, the email column in students allows NULL. Let us fix that.

Important:Best practice: clean data before adding NOT NULL

Before adding NOT NULL to a column, fix any rows with NULL values. MySQL rejects ALTER TABLE ... NOT NULL if existing rows contain NULLs. A common pattern is to run an UPDATE first:

UPDATE students
SET email = CONCAT(name, '@school.edu')
WHERE email IS NULL;

In this tutorial, all students already have emails, so this step is not needed. But in a real database, always check for NULLs before tightening constraints.

Add NOT NULL to email

ALTER TABLE students
MODIFY COLUMN email VARCHAR(100) NOT NULL;

This changes the email column to reject NULL values. Any future INSERT or UPDATE that leaves email empty will fail.

Try inserting without an email

INSERT INTO students (name) VALUES ('Diana');

This fails with Error 1364: Field 'email' doesn't have a default value. The NOT NULL constraint requires a value for email.

UNIQUE

UNIQUE prevents duplicate values in a column. Right now, two students could have the same email address. Let us prevent that.

Add UNIQUE constraint on email

ALTER TABLE students
ADD CONSTRAINT unique_email
UNIQUE (email);

This ensures no two students can have the same email address. The constraint name 'unique_email' appears in error messages.

Try inserting a duplicate email

INSERT INTO students (name, email) VALUES ('Diana', '[email protected]');

This fails with Error 1062: Duplicate entry '[email protected]' for key 'students.unique_email'. Alice already has this email.

Important:UNIQUE and NULL

UNIQUE allows multiple NULL values. Two rows with NULL in a UNIQUE column are not considered duplicates because NULL is not equal to NULL. This is standard SQL behavior.

DEFAULT

DEFAULT provides a fallback value when INSERT omits the column. Let us add a timestamp to track when enrollments were created.

Add a column with DEFAULT

ALTER TABLE enrollments
ADD COLUMN enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

New column enrolled_at defaults to the current timestamp. Existing rows get the current time as their value.

Insert without specifying enrolled_at

INSERT INTO enrollments (student_id, course_name, score) VALUES (3, 'Science', 92);

enrolled_at is not specified, so it defaults to CURRENT_TIMESTAMP. You can verify with: SELECT * FROM enrollments WHERE student_id = 3;

Override the default

INSERT INTO enrollments (student_id, course_name, score, enrolled_at)
VALUES (2, 'Science', 88, '2026-01-10 09:00:00');

Specifying enrolled_at overrides the default. The enrollment is backdated to January 10.

CHECK

CHECK constraints enforce custom validation rules on column values. MySQL 8.0.16+ enforces them; earlier versions parse but ignore them.

Custom rules:CHECK constraints

A CHECK constraint defines a condition that must be true for every row. If the condition evaluates to FALSE or NULL, the INSERT or UPDATE is rejected.

  • Can reference a single column (e.g., score >= 0)
  • Can reference multiple columns (e.g., end_date >= start_date)
  • Named constraints make error messages clearer

Add a CHECK constraint on score

ALTER TABLE enrollments
ADD CONSTRAINT chk_score
CHECK (score >= 0 AND score <= 100);

Score must be between 0 and 100. Any INSERT or UPDATE with a score outside this range is rejected.

Try inserting an invalid score

INSERT INTO enrollments (student_id, course_name, score) VALUES (3, 'Math', 150);

This fails with Error 3819: Check constraint 'chk_score' is violated. The score 150 exceeds the maximum of 100.

Try inserting a negative score

UPDATE enrollments SET score = -5 WHERE id = 1;

Also fails. The CHECK constraint ensures scores stay within 0 to 100.

FOREIGN KEY

The enrollments table already has a FOREIGN KEY from Stage 7: student_id references students(id). This prevents enrolling a student that does not exist.

Try enrolling a nonexistent student

INSERT INTO enrollments (student_id, course_name, score) VALUES (999, 'Math', 80);

This fails with Error 1452: Cannot add or update a child row. Student id 999 does not exist in the students table. The FOREIGN KEY prevents orphaned enrollment records.

Key choices:ON DELETE Actions

When a parent row is deleted, the FOREIGN KEY determines what happens to child rows:

  • CASCADE: delete child rows automatically
  • RESTRICT: block the parent deletion if children exist
  • SET NULL: set the foreign key column to NULL in child rows
  • NO ACTION: same as RESTRICT in MySQL

What does ON DELETE CASCADE do?

A UNIQUE column can contain multiple NULL values?

Key Takeaways

  • NOT NULL prevents missing values; UNIQUE prevents duplicates (multiple NULLs allowed).
  • DEFAULT provides automatic values when INSERT omits a column.
  • CHECK enforces custom rules like score ranges.
  • FOREIGN KEY ensures child rows reference valid parent rows.
  • ON DELETE actions (CASCADE, RESTRICT, SET NULL) control what happens when a parent row is deleted.

Ready to test your knowledge?

Take a Quiz

Related Blog Posts

Related Tutorials