SQL Constraints: All 6 Types Explained with MySQL Syntax Examples

Across this entire module, constraints have appeared one at a time — NOT NULL here, FOREIGN KEY there. This lesson brings all six together as a single reference: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT. Seeing them side by side makes it much easier to choose the right combination when designing a brand-new table from scratch.

What Is a Constraint, Restated

A constraint is a rule enforced by the database itself on a column or table, restricting what data can be stored to keep it valid and consistent. Constraints can be defined at the column level (attached to a single column's definition) or at the table level (defined separately, often used when a rule spans multiple columns, like a composite key or a foreign key).

What You'll Learn

• List and define all six major SQL constraint types.
• Distinguish column-level constraint syntax from table-level constraint syntax.
• Combine multiple constraints correctly on a single table.
• Know how to name and later drop a constraint if needed.

Key Terms to Know

• NOT NULL: Requires a column to always have a value.
• UNIQUE: Disallows duplicate values within a column (or column combination).
• PRIMARY KEY: Uniquely identifies each row; combines NOT NULL and UNIQUE, one per table.
• FOREIGN KEY: Enforces that a column's values match an existing primary key in another table.
• CHECK: Validates a column or row against a custom boolean condition.
• DEFAULT: Supplies an automatic value when none is given during INSERT.

The Six Constraint Types, Side by Side

NOT NULL guarantees a value is always present. UNIQUE guarantees no duplicates. PRIMARY KEY combines both of these and additionally marks the column (or columns) as the row's unique identifier — limited to one definition per table, though it can be composite. FOREIGN KEY links a column to another table's primary key, enforcing referential integrity. CHECK validates values against a custom rule, like price > 0. DEFAULT auto-fills a value when none is supplied.

Each of the previous five lessons in this module covered one or more of these in depth — this lesson is the consolidated reference point that ties them together.

Column-Level vs Table-Level Constraint Syntax

A column-level constraint is written immediately after that column's data type, such as email VARCHAR(150) UNIQUE NOT NULL. This style is concise and works well when the constraint applies to exactly one column.

A table-level constraint is written as a separate clause, often used for composite keys or named constraints: CONSTRAINT chk_price CHECK (price > 0), or PRIMARY KEY (student_id, course_id). Table-level syntax is required whenever a constraint spans more than one column, and is also useful when you want to give a constraint an explicit name for easier management later.

Naming and Dropping Constraints

Giving a constraint an explicit name with CONSTRAINT constraint_name ... makes it much easier to reference later, particularly when removing it with ALTER TABLE table_name DROP CONSTRAINT constraint_name; (or DROP FOREIGN KEY / DROP CHECK depending on MySQL version and constraint type).

Without an explicit name, MySQL auto-generates one, which is harder to predict and reference in later ALTER TABLE statements. For any constraint likely to need future changes, naming it explicitly during CREATE TABLE is good practice.

Visual Summary

Picture a finished table as a fully staffed security checkpoint with six different checks running on every piece of data trying to get in: NOT NULL checks 'is anything missing?', UNIQUE checks 'has this exact value been seen before?', PRIMARY KEY checks 'does this row have a valid ID badge, and is it the only one with that badge?', FOREIGN KEY checks 'does this reference actually exist elsewhere?', CHECK runs a custom rule like 'is this value within an acceptable range?', and DEFAULT quietly fills in a value if nothing was handed over at all.

All Six SQL Constraint Types

Constraint	Enforces	Per Table
NOT NULL	Value must be present	Any number of columns
UNIQUE	No duplicate values	Any number of columns/combinations
PRIMARY KEY	Unique row identifier (NOT NULL + UNIQUE)	Exactly one (can be composite)
FOREIGN KEY	Value must exist in referenced parent table	Any number of columns
CHECK	Custom boolean validation rule	Any number of rules
DEFAULT	Auto-fill value if none provided	Any number of columns

SQL Example

-- A single table demonstrating all six constraint types together
CREATE TABLE course_enrollments (
  enrollment_id INT            PRIMARY KEY AUTO_INCREMENT,
  student_id    INT            NOT NULL,
  course_id     INT            NOT NULL,
  email_used    VARCHAR(150)   NOT NULL UNIQUE,
  grade_percent DECIMAL(5, 2)  DEFAULT NULL,
  status        VARCHAR(20)    NOT NULL DEFAULT 'active',
  CONSTRAINT chk_grade_range CHECK (grade_percent IS NULL OR grade_percent BETWEEN 0 AND 100),
  CONSTRAINT uq_student_course UNIQUE (student_id, course_id),
  CONSTRAINT fk_enrollment_student FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
  CONSTRAINT fk_enrollment_course FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE
);

This single table uses all six constraint types: PRIMARY KEY on enrollment_id, NOT NULL on several columns, UNIQUE both on email_used and as a composite (student_id, course_id) pairing, FOREIGN KEY linking to students and courses, CHECK validating grade_percent stays within a sane range, and DEFAULT auto-filling status as 'active'. Naming each table-level constraint (chk_grade_range, uq_student_course, etc.) makes them easy to reference if they ever need to be dropped or modified later.

Real-World Examples

• Banking schemas combine NOT NULL, CHECK, and FOREIGN KEY heavily on account and transaction tables to guarantee both completeness and referential correctness.
• E-commerce platforms rely on UNIQUE and CHECK together to prevent duplicate SKUs and invalid pricing simultaneously.
• Learning platforms use composite UNIQUE constraints, just like in the example above, to prevent a student from enrolling in the same course twice.
• Healthcare systems use CHECK constraints extensively to enforce valid ranges for clinical values, alongside strict NOT NULL rules for required patient identifiers.

Best Practices and Pro Tips

• Name your constraints explicitly during CREATE TABLE, especially CHECK and UNIQUE constraints that might need to be modified or dropped later.
• Think through all six constraint types deliberately for every new table during design — it's far cheaper to add the right constraint upfront than to clean up bad data after the fact.
• Combine constraints thoughtfully rather than over-constraining; for example, don't add CHECK rules so strict they reject valid edge cases your business actually needs to support.

Common Mistakes to Avoid

• Treating PRIMARY KEY and UNIQUE as interchangeable — a table can have many UNIQUE constraints but only one PRIMARY KEY.
• Forgetting that a composite UNIQUE or PRIMARY KEY constraint requires the combination, not either column alone, to be duplicate-free.
• Skipping constraint names, making future ALTER TABLE DROP CONSTRAINT statements harder to write correctly.
• Adding constraints retroactively on a live table without first checking whether existing data already violates the new rule.

Interview Questions

Q1. Can you name all six major SQL constraint types?

NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT. NOT NULL and UNIQUE enforce presence and distinctness, PRIMARY KEY uniquely identifies rows, FOREIGN KEY enforces cross-table referential integrity, CHECK validates custom rules, and DEFAULT auto-fills missing values.

Q2. What is the difference between a column-level and table-level constraint?

A column-level constraint is defined directly within a single column's definition. A table-level constraint is defined as a separate clause, required when a constraint spans multiple columns, such as a composite primary key or unique constraint.

Q3. Why would you give a constraint an explicit name?

Naming a constraint with CONSTRAINT constraint_name makes it much easier to reference later when modifying or dropping it with ALTER TABLE, compared to relying on MySQL's auto-generated, less predictable default name.

Q4. Can a single table have multiple CHECK constraints?

Yes, a table can have any number of CHECK constraints, each validating a different rule across one or more columns, as long as MySQL version 8.0.16 or later is enforcing them.

Practice MCQs

1. Which constraint combines both NOT NULL and UNIQUE automatically?

1. FOREIGN KEY
2. CHECK
3. PRIMARY KEY
4. DEFAULT

Answer: C. PRIMARY KEY

Explanation: PRIMARY KEY automatically enforces both NOT NULL and UNIQUE on the column(s) it's applied to.

2. Which constraint type is required to span multiple columns in a composite key, written at the table level?

1. Column-level NOT NULL
2. Table-level PRIMARY KEY (col1, col2)
3. DEFAULT
4. CHECK on a single column

Answer: B. Table-level PRIMARY KEY (col1, col2)

Explanation: A composite key spanning multiple columns must be written as a table-level constraint, since it can't be expressed within a single column's definition.

3. How many PRIMARY KEY constraints can a single table have?

1. As many as needed
2. Exactly one
3. Exactly two
4. Zero, only UNIQUE is allowed

Answer: B. Exactly one

Explanation: A table can have only one PRIMARY KEY definition, although that single primary key can be composite, spanning multiple columns.

Quick Revision Points

• The six constraint types: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT.
• PRIMARY KEY = NOT NULL + UNIQUE, limited to one per table (composite allowed).
• Composite constraints (UNIQUE or PRIMARY KEY across multiple columns) require table-level syntax.
• Explicit constraint naming improves maintainability for future ALTER TABLE changes.

Conclusion

• All six constraint types work together, not in isolation, to fully protect data integrity in a well-designed table.
• Knowing column-level versus table-level syntax is essential for composite keys and named constraints.
• Thoughtful constraint design at CREATE TABLE time prevents the majority of data quality issues later.

NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT together form the complete toolkit of SQL constraints, each protecting a different aspect of data integrity — presence, distinctness, identity, cross-table correctness, custom validation, and automatic fallback values. Used deliberately and combined correctly, they shift data quality enforcement from application code into the database itself, where it can never be accidentally bypassed. With every individual DDL concept now covered, the final lesson in this module ties it all together in a practical exercise: designing a complete 3-table student management schema.