PostgresqlHow-ToBeginner · 3 min read

How to Add Check Constraint in PostgreSQL: Syntax and Examples

In PostgreSQL, you add a check constraint using the ALTER TABLE statement with ADD CONSTRAINT and CHECK clause. This ensures that data in a column meets a specific condition before it is saved.

📐

Syntax

The basic syntax to add a check constraint in PostgreSQL is:

ALTER TABLE table_name: specifies the table to modify.
ADD CONSTRAINT constraint_name: names the new constraint.
CHECK (condition): defines the condition that data must satisfy.

sql

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (condition);

💻

Example

This example adds a check constraint to ensure that the age column in the users table only contains values greater than or equal to 18.

sql

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  age INT
);

ALTER TABLE users
ADD CONSTRAINT age_check CHECK (age >= 18);

Output

CREATE TABLE ALTER TABLE

⚠️

Common Pitfalls

Common mistakes when adding check constraints include:

Not naming the constraint, which can make it harder to manage later.
Writing incorrect conditions that always evaluate to true or false.
Adding constraints that conflict with existing data, causing errors.

Example of a wrong and right way:

sql

-- Wrong: Missing constraint name
ALTER TABLE users
ADD CHECK (age >= 18);

-- Right: Named constraint
ALTER TABLE users
ADD CONSTRAINT age_check CHECK (age >= 18);

📊

Quick Reference

Command	Description
ALTER TABLE table_name	Selects the table to modify
ADD CONSTRAINT constraint_name	Names the new constraint
CHECK (condition)	Defines the condition data must meet
DROP CONSTRAINT constraint_name	Removes an existing constraint

✅

Key Takeaways

Use ALTER TABLE with ADD CONSTRAINT and CHECK to add a check constraint.

Always name your constraints for easier management.

Ensure the check condition correctly reflects the data rule you want.

Check existing data before adding constraints to avoid errors.

Use DROP CONSTRAINT to remove constraints if needed.