SQL from Zero › Module 3: DML: filling and changing data
When rules are broken
Read constraint errors like a pro and fix what caused them.
Lesson 16 of 33 · about 12 minutes
Video: The database says noThe narrated video for this lesson is coming soon. The lesson below covers the same ideas.
The lesson
In Module 2 you gave your tables rules. Now you'll see them protect the data. When a change breaks a rule, the database refuses the whole statement and explains why. Nothing is half-done.
Here are the errors you'll meet most, and what they mean in plain words:
| Error message | What it means | Typical fix |
|---|---|---|
NOT NULL constraint failed: books.title | A required value is missing | Provide the value |
UNIQUE constraint failed: books.id | That value is already taken | Use a different value, or leave the id out |
FOREIGN KEY constraint failed | You pointed to something that doesn't exist | Use an id that exists in the other table |
CHECK constraint failed | The value failed a test, like a negative price | Fix the value |
The message names the rule and usually the column. Read it slowly. It's the database telling you exactly where to look.
These errors are good news. Each one is bad data that didn't get into your shop.
Key ideas
- A change that breaks a rule is refused completely, with an error that names the rule.
- NOT NULL means a value is missing; UNIQUE means it's taken; FOREIGN KEY means it points to nothing.
- Constraint errors are the database protecting your data.
Practise
Loading practice…