Constraints
Get a conceptual overview of what constraints are in MySQL and why they are essential for keeping your data accurate and consistent.
Introduction
So far, you have created tables and defined columns with data types. But data types alone do not stop someone from inserting a student with a duplicate ID, a negative age, or a missing name. That is the job of constraints.
This lesson gives you a conceptual overview of what constraints are and why they matter, before the next several lessons cover each type of constraint in depth, one at a time.
- What a constraint is, in plain terms.
- The main types of constraints MySQL supports.
- Why enforcing rules on data matters more than fixing bad data later.
- A quick preview of constraints applied together on a table.
What is a Constraint?
A constraint is a rule enforced on a column (or a set of columns) to maintain the accuracy and integrity of the data stored in a table. Constraints are defined when a table is created, or added afterward with ALTER TABLE.
Think of a constraint as a gatekeeper: before any row is inserted or updated, MySQL checks it against every constraint on the table. If a row would break a rule, MySQL rejects it and the bad data never gets stored in the first place.
Constraints apply to the whole table, for every row, all the time — not just once when the table is created. Every future INSERT or UPDATE is checked against them automatically.
Types of Constraints
MySQL supports several kinds of constraints, each enforcing a different kind of rule. The next several lessons in this course cover each one individually, in depth — this table is just a map of what is coming.
| Constraint | What It Enforces |
|---|---|
| PRIMARY KEY | Uniquely identifies every row; cannot be NULL. |
| FOREIGN KEY | Links a column to the primary key of another table, keeping references valid. |
| UNIQUE | Ensures every value in a column is different across all rows. |
| DEFAULT | Automatically fills in a value when none is provided. |
| CHECK | Ensures a value satisfies a specific condition, like age > 0. |
| NOT NULL | Ensures a column can never be left empty. |
Why Constraints Matter
Without constraints, it is entirely possible to insert a student with no name, two students sharing the exact same id, or a grade referencing a class that does not exist. Nothing would stop it at the moment of insertion.
Constraints shift data validation from "fix bad data after it is discovered" to "prevent bad data from ever being stored." This is far more reliable than hoping every application, script, or person that touches the database always remembers to check the rules themselves.
Prevention Over Cleanup
Invalid data is rejected immediately, instead of needing to be found and fixed later.
Guaranteed Rules
Rules are enforced by the database itself, regardless of which application or script is inserting data.
Trustworthy Relationships
Constraints like FOREIGN KEY keep links between tables meaningful and valid.
A Worked Preview
Here is a small preview of a students table with two constraints applied together: a PRIMARY KEY on id, and a NOT NULL rule on name. Each of these, along with the others in the table above, gets its own full lesson very soon.
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT,
grade VARCHAR(2)
);
-- This fails: id 1 is already used, breaking PRIMARY KEY
INSERT INTO students (id, name, age, grade)
VALUES (1, 'Alex', 16, 'A');
INSERT INTO students (id, name, age, grade)
VALUES (1, 'Sam', 17, 'B');ERROR 1062 (23000): Duplicate entry '1' for key 'students.PRIMARY'MySQL rejected the second row before it was ever stored, because it violated the PRIMARY KEY constraint. This is exactly the kind of protection constraints exist to provide.
Common Mistakes
- Designing tables without any constraints and relying only on application code to validate data.
- Assuming constraints can be added at any time without checking whether existing data already violates them.
- Treating all constraints as interchangeable, when each one enforces a very different kind of rule.
- Forgetting that a violated constraint causes the entire INSERT or UPDATE to be rejected, not just a warning.
Best Practices
- Add constraints when you first design a table, rather than as an afterthought.
- Use NOT NULL on any column that should never realistically be empty.
- Use PRIMARY KEY and FOREIGN KEY together to keep related tables consistent.
- Learn each constraint type individually before combining several on the same table.
Frequently Asked Questions
Can a single column have more than one constraint?
Yes. For example, a column can be both PRIMARY KEY and enforce NOT NULL and UNIQUE at the same time, since a primary key already implies both of those rules.
Do constraints slow down my database?
Constraints add a small amount of checking work on INSERT and UPDATE, but this cost is almost always worth it compared to the risk of storing invalid data.
Can I add a constraint to a table that already has data?
Yes, using ALTER TABLE, but MySQL will check the existing data against the new rule first, and the change will fail if any existing row already violates it.
Which constraint should I learn first?
PRIMARY KEY, since almost every table needs one and several other constraints (like FOREIGN KEY) depend on it directly. It is covered in the very next lesson.
Key Takeaways
- A constraint is a rule enforced on a column to maintain data accuracy and integrity.
- MySQL supports PRIMARY KEY, FOREIGN KEY, UNIQUE, DEFAULT, CHECK, and NOT NULL constraints.
- Constraints prevent invalid data from ever being stored, rather than requiring cleanup afterward.
- A single table can combine multiple constraints across multiple columns.
- Each constraint type is covered individually in the lessons that follow this one.
Summary
Constraints are the rules that keep a database trustworthy. Rather than hoping every insert is correct, constraints let the database itself enforce accuracy automatically, for every row, every time.
In this lesson, you got a conceptual overview of constraints and saw a small preview combining PRIMARY KEY and NOT NULL. Next, you will study the PRIMARY KEY constraint in full detail.
- You understand what a constraint is.
- You can name the six main types of constraints in MySQL.
- You understand why constraints prevent bad data instead of just catching it later.
- You are ready to study each constraint type individually, starting with PRIMARY KEY.