Working with the CHECK constraint for data validation

The CHECK constraint is kind of like a bouncer at the entrance to your table. It makes sure that the data you add to the table matches certain conditions. If you try to put in data that breaks those rules, the database just won't let it in.

Imagine you want to open a store in Germany. But by law, Sunday is a day off, and you can't do business on that day. This restriction is like a CHECK in a database. You try to set the store schedule: open 7 days a week, but the system immediately replies: "Nein, nein, Sunday is a violation. That schedule won't pass the check!"

Same thing in a database: if you set a value that breaks the CHECK rule, the system blocks it so you don't end up with a "logical error" in your data.

Why do you need CHECK?

1. Keeping your data clean: CHECK stops bad or illogical info from getting into your table.
2. Less chance of mistakes: instead of checking data by hand before inserting, you can let the database handle it for you.
3. Logic built right in: you can bake your validation rules into the database structure, instead of relying on your app code to do it.

How does CHECK work?

You set up a CHECK constraint when you create a table, or you can add it later with the ALTER TABLE command. Here's the basic syntax:

CREATE TABLE table_name (
    column data_type CHECK (condition)
);

condition is a logical expression that has to be true for every value in that column. If the condition is broken, the database throws an error.

Example 1: Checking value ranges

Let's make a students table where the students' age (age) has to be between 16 and 100:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INTEGER CHECK (age >= 16 AND age <= 100)
);

Now, if you try to insert a student who's 12, the database will "catch you red-handed":

INSERT INTO students (name, age)
VALUES ('Maria Chi', 12);

Error:

ERROR:  new row for relation "students" violates check constraint "students_age_check"
DETAIL:  Failing row contains (1, Maria Chi, 12).

Yeah, the database here is a tough gatekeeper. No 16 — no entry.

Example 2: Checking data format

Say you have an emails table with a list of email addresses. You want to make sure every address has an @ symbol (this is a super simple check):

CREATE TABLE emails (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) CHECK (email LIKE '%@%')
);

Let's try to add a bad address that doesn't match the rule:

INSERT INTO emails (email)
VALUES ('notanemail.com');

Error:

ERROR:  new row for relation "emails" violates check constraint "emails_email_check"
DETAIL:  Failing row contains (1, notanemail.com).

You can avoid the error if all your data has the @ symbol:

INSERT INTO emails (email) 
VALUES ('example@student.com');

The query will run just fine.

Example 3: Checking conditions across multiple columns

CHECK constraints can check not just one column, but a logical expression that involves several columns. Let's look at an employees table where the salary (salary) has to be bigger than the bonus (bonus):

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    salary NUMERIC CHECK (salary > 0),
    bonus NUMERIC CHECK (bonus >= 0),
    CHECK (salary > bonus)
);

Now, if someone tries to add an employee whose bonus is bigger than their salary, the database won't allow it:

INSERT INTO employees (name, salary, bonus)
VALUES ('Otto Lin', 3000, 4000);

Error:

ERROR:  new row for relation "employees" violates check constraint "employees_salary_bonus_check"
DETAIL:  Failing row contains (1, Otto Lin, 3000, 4000).

Real-world use

The CHECK constraint is super helpful when your business logic is tightly connected to data restrictions. For example:

1. Online stores: blocking products with negative prices.
2. Education platforms: checking the age of course participants.
3. Medical systems: making sure a patient's body temperature is within allowed limits.

These checks aren't just an extra safety net — they save time and nerves for both devs and users.

Things to watch out for and common mistakes

When you're working with CHECK, keep these things in mind:

• Logical expressions in CHECK have to be true for every row in the table. If even one row breaks the rule, you'll need to fix it before adding the constraint.

• The check won't run if the value you're inserting is NULL. In other words, CHECK (age >= 18) won't throw an error for age = NULL. That's because any expression with NULL in it automatically becomes undefined. If you want to block NULL, add NOT NULL.

• Complicated CHECK conditions can slow down inserts and updates, especially in big tables.