How to Use Check Constraints in Supabase for Data Integrity

Data types are great, but they are often too "blunt" for real-world business logic. You might know a column is an integer, but you also need to ensure that it's never a negative number, or that a discount_price is always lower than the original_price.

In Supabase (which runs on PostgreSQL), the Check Constraint is your first line of defense for data integrity. It sits directly in the database engine, meaning no matter how a user tries to insert data (API, Dashboard, or CLI), the rule is impossible to bypass.

🛠️ How to Add a Check Constraint

The easiest way to add a constraint in Supabase is via the SQL Editor in your Dashboard.

Basic Syntax

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (condition);

💻 Practical Examples

Here are the four most common check constraints you'll actually use in a Supabase project:

1. Ensuring Positive Values (Price/Stock)

ALTER TABLE products
ADD CONSTRAINT positive_price CHECK (price > 0);

2. Comparing Two Columns (Discount Logic)

ALTER TABLE products
ADD CONSTRAINT discount_less_than_price
CHECK (discount_price < price);

3. Restricting Strings to a List (Status/Role) Note: While ENUMs exist, Check Constraints are often easier to modify later.

ALTER TABLE orders
ADD CONSTRAINT valid_status
CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled'));

4. Minimum String Length (Username/Bio)

ALTER TABLE profiles
ADD CONSTRAINT username_length
CHECK (char_length(username) >= 3);

---

🚀 Adding Constraints Without Downtime

If you have a table with millions of rows, running a standard ALTER TABLE will lock the table while Postgres checks every single existing row. This can cause your Supabase app to hang.

In 2026, the professional way to do this is a two-step "Safe Migration":

1. Add the constraint as NOT VALID: This applies the rule to all new data immediately but ignores the old data for now. It does not lock the table.

   ALTER TABLE large_table
   ADD CONSTRAINT my_check CHECK (score > 0) NOT VALID;

2. Validate it later: This tells Postgres to check the old rows in the background without blocking users.

   ALTER TABLE large_table VALIDATE CONSTRAINT my_check;

---

📊 Managing Your Constraints

Task	SQL Command
List all constraints	SELECT * FROM information_schema.table_constraints;
Delete a constraint	ALTER TABLE table_name DROP CONSTRAINT constraint_name;
Rename a constraint	ALTER TABLE table_name RENAME CONSTRAINT old_name TO new_name;

---

🛡️ What happens when a Check fails?

When a user tries to insert bad data via the Supabase client (supabase-js), the database will reject it and return a PostgREST error.

const { error } = await supabase
  .from('products')
  .insert({ price: -10 }) // This violates our 'positive_price' check

console.log(error.message)
// Output: "new row for relation 'products' violates check constraint 'positive_price'"

Pro Tip: Always give your constraints descriptive names (like price_must_be_positive instead of check_1). These names show up directly in your frontend error messages, making debugging 10x faster.

---

📚 Sources & Technical Refs

• [1.1] PostgreSQL Docs: Check Constraints - The core technical specification.
• [2.1] Supabase Guides: Database Management - Best practices for schema design.
• [3.1] PostgresAI: Zero-downtime Constraints - Safe migration patterns for production.