The PostgreSQL Check Constraint That Silently Corrupted User Data

"We need to ensure users can only have certain status values."

So I added a CHECK constraint.

What I didn't expect: The constraint would happily accept bad data on INSERT, then block all future UPDATES – corrupting thousands of records.

The Setup

User table with a status column:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL,
  status TEXT DEFAULT 'active'
);

-- Add constraint to limit status values
ALTER TABLE users 
ADD CONSTRAINT valid_status 
CHECK (status IN ('active', 'inactive', 'banned'));

Simple enough.

The Problem

Months later, we introduced a new status: 'pending_verification'.

We updated the application code to use it, but forgot to update the CHECK constraint.

When the app tried to insert a user with status 'pending_verification':

INSERT INTO users (email, status) VALUES ('new@example.com', 'pending_verification');
-- ERROR: check constraint "valid_status" violated

The app caught this error and retried with a fallback status.

But here's where it gets bad: The fallback logic had a bug. Instead of retrying, it set status to NULL.

// Buggy fallback
try {
  await db.query('INSERT INTO users...', ['new@example.com', 'pending_verification']);
} catch (err) {
  // Fallback to NULL (BAD!)
  await db.query('INSERT INTO users...', ['new@example.com', null]);
}

The CHECK constraint allowed NULL (because NULL is not 'active','inactive','banned'? Actually NULL passes the IN check? No – NULL IN (...) returns NULL, which CHECK treats as true. So NULL was allowed.)

Result: 10,000 users with status = NULL were inserted over 3 months.

The Real Disaster

When we finally added 'pending_verification' to the CHECK constraint:

ALTER TABLE users DROP CONSTRAINT valid_status;
ALTER TABLE users ADD CONSTRAINT valid_status 
CHECK (status IN ('active', 'inactive', 'banned', 'pending_verification'));

Everything seemed fine.

But then attempts to UPDATE existing NULL status users to 'active' started failing:

UPDATE users SET status = 'active' WHERE status IS NULL;
-- ERROR: check constraint "valid_status" violated

Why? Because the new constraint didn't allow NULL. But the table already had NULL values.

PostgreSQL doesn't validate existing rows when you add a constraint unless you use "NOT VALID". We didn't.

So the constraint was enforced for new writes but ignored existing invalid rows. This created a split-brain state:

• Old NULL rows could never be updated to any valid status (because the UPDATE would check the constraint and fail, since NULL is not allowed)
• New rows could only be inserted with allowed values

We had 10,000 rows stuck with NULL forever – unless we dropped the constraint, fixed them, and re-added it.

The Fix

1. Find all rows violating the new constraint

SELECT COUNT(*) FROM users WHERE status NOT IN ('active', 'inactive', 'banned', 'pending_verification') AND status IS NOT NULL;
-- Also handle NULL separately
SELECT COUNT(*) FROM users WHERE status IS NULL;

2. Fix the rows

-- Update NULL rows to a default
UPDATE users SET status = 'pending_verification' WHERE status IS NULL;
-- Now constraint will pass

3. Re-add constraint with validation

-- Add but don't validate existing rows yet
ALTER TABLE users ADD CONSTRAINT valid_status 
CHECK (status IN ('active', 'inactive', 'banned', 'pending_verification'))
NOT VALID;

-- Validate in a separate transaction (can take a while)
ALTER TABLE users VALIDATE CONSTRAINT valid_status;

4. Add default and NOT NULL

ALTER TABLE users ALTER COLUMN status SET NOT NULL;
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'pending_verification';

The Prevention

Always use NOT VALID + VALIDATE for large tables

-- Don't do this on large tables (takes exclusive lock)
ALTER TABLE users ADD CONSTRAINT ... CHECK (...);

-- Do this instead
ALTER TABLE users ADD CONSTRAINT ... CHECK (...) NOT VALID;
-- No lock, then later:
ALTER TABLE users VALIDATE CONSTRAINT ...; -- Share lock only

Test NULL behavior

-- Check how NULL behaves with your constraint
SELECT NULL IN ('active', 'inactive');  -- Returns NULL, not false
-- In CHECK, NULL is treated as TRUE (row passes)

Use NOT NULL with CHECK

-- Better pattern
status TEXT NOT NULL CHECK (status IN ('active', 'inactive', 'banned'))

Add constraints in transactions with validation scripts

BEGIN;
-- First, ensure all data complies
UPDATE users SET status = 'active' WHERE status IS NULL;
-- Then add constraint
ALTER TABLE users ADD CONSTRAINT valid_status CHECK (status IN ('active', 'inactive', 'banned', 'pending_verification'));
COMMIT;

Commands to Audit Constraints

List all constraints with NOT VALID

SELECT conname, conrelid::regclass, convalidated
FROM pg_constraint
WHERE convalidated = false;

Check which rows violate a constraint

-- For CHECK constraint named 'valid_status' on 'users'
SELECT * FROM users WHERE NOT (status IN ('active', 'inactive', 'banned', 'pending_verification'));

Force validation with error reporting

-- Will fail and show violating rows
ALTER TABLE users VALIDATE CONSTRAINT valid_status;
-- If fails, query pg_constraint for details

What I Learned

• CHECK constraints with NULLs are tricky – NULL passes any CHECK unless you explicitly forbid NULL.
• Adding a constraint to a large table without "NOT VALID" locks the table and can cause downtime.
• Application fallbacks that insert NULL are dangerous.
• Always test constraints with edge values (NULL, empty string, whitespace).

That silent corruption went undetected for 3 months. Now we have automated tests that verify constraints reject invalid data and allow valid updates.