In this lesson, you will learn how to use CHECK constraints to enforce business-specific rules that automatically validate the data being saved to your tables.
The Problem of "Bad" Data
In our previous lessons, we learned how to design tables and modify schemas. However, a table definition like price DECIMAL is often too permissive—it allows negative numbers, which makes no sense for a product price. Without extra rules, your database might accept "dirty" data that ruins your reports and calculations later.
Introducing CHECK Constraints
A CHECK constraint acts as a gatekeeper for your columns. Before the database allows an INSERT or an UPDATE to proceed, it evaluates a logical condition. If the condition is true, the change is saved. If it is false, the database rejects the command with an error, ensuring your data quality remains pristine.
Think of it as a permanent WHERE clause that lives inside your table definition.
Implementation Example
Let’s say we are managing a library system. We want to ensure that a book’s rating is always between 1 and 5, and that the publish_year is not in the future.
CREATE TABLE books (
book_id INT PRIMARY KEY,
title VARCHAR(100),
rating INT CHECK (rating >= 1 AND rating <= 5),
publish_year INT CHECK (publish_year <= 2024)
);
When you try to run an insert command like INSERT INTO books VALUES (1, 'SQL Guide', 10, 2025);, the database will stop you immediately. Because 10 is not less than or equal to 5, and 2025 is greater than 2024, the operation will fail.
By defining these constraints at the schema level, you remove the burden of validation from your application code and guarantee that no matter how data is added to the database, it will always follow your business logic. This protects your data from human error and logic bugs.
Key Takeaway: CHECK constraints act as automated, permanent guardians for your table columns, ensuring that only data meeting your predefined business logic is ever allowed to be stored.