10 Database Schema Changes That Will Break Production

Every database migration carries risk. Some changes are harmless. Others will take down production the moment they run. After reviewing thousands of schema diffs and talking to engineers who've lived through 3 AM outages, we've identified the 10 schema changes most likely to cause incidents.

For each change below, you'll see:

• Why it breaks — the failure mode in production
• Real example — actual SQL that caused an incident
• Risk score — how SchemaLens rates the danger (0–100)
• How to prevent it — the safe migration pattern

1. Dropping a Column That's Still Used Risk: 95

The most common production-breaking schema change. You drop a column that the application still references. The next request that touches that column throws an error.

-- DANGEROUS: Do not run without verifying zero references
ALTER TABLE users DROP COLUMN legacy_api_token;

What goes wrong: A background job, reporting query, or old API endpoint still selects legacy_api_token. The application code was updated, but a cron job in a separate repository wasn't.

Safe pattern:

1. Search every codebase for references to the column
2. Remove all references and deploy
3. Wait 1–2 deploy cycles to confirm no errors
4. Then drop the column

2. Adding NOT NULL Without a Default Risk: 90

PostgreSQL will reject this if any existing row has a NULL value. MySQL will silently update NULLs to implicit defaults (which can be worse). Either way, it's dangerous.

-- DANGEROUS on PostgreSQL: fails if NULLs exist
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

-- SAFE: Add default first, then enforce NOT NULL
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending';
UPDATE orders SET status = 'pending' WHERE status IS NULL;
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

Safe pattern: Add the column as nullable with a default, backfill existing rows, then add the NOT NULL constraint. In PostgreSQL 11+, you can do this in a single statement with ALTER TABLE ... ADD COLUMN ... NOT NULL DEFAULT 'value' without a table rewrite.

3. Renaming a Column or Table Risk: 85

Renaming breaks every query, ORM model, and report that references the old name. It's an atomic change with zero backward compatibility.

-- DANGEROUS: Instant breakage across all code
ALTER TABLE users RENAME COLUMN email TO email_address;

Safe pattern: Don't rename in place. Create a new column, dual-write to both, migrate reads, then drop the old column. For tables, create a view with the old name as an alias during transition.

4. Narrowing a Column Type Risk: 80

Changing VARCHAR(500) to VARCHAR(100) or INT to SMALLINT will fail when existing data doesn't fit.

-- DANGEROUS: Fails if any value > 100 chars
ALTER TABLE products ALTER COLUMN name TYPE VARCHAR(100);

Safe pattern: Check current data distribution first:

SELECT MAX(LENGTH(name)) FROM products;
-- Only narrow if max length is safely under the new limit

5. Dropping an Index Used by Queries Risk: 70

You drop what looks like a duplicate index, but it's actually the one powering your most critical query. Latency spikes. Timeouts cascade.

-- DANGEROUS without checking query plans
DROP INDEX idx_orders_user_id;

Safe pattern: Use pg_stat_user_indexes (PostgreSQL) or sys.dm_db_index_usage_stats (SQL Server) to verify the index hasn't been used recently. Even then, monitor query performance for 24–48 hours after dropping.

6. Adding a UNIQUE Constraint on Duplicates Risk: 85

You assume a column is unique, but production data says otherwise. The migration fails partway through, leaving a partial transaction.

-- DANGEROUS: Fails if duplicates exist
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

Safe pattern: Verify uniqueness first:

SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;
-- Clean duplicates before adding constraint

7. Changing Foreign Key Cascade Rules Risk: 65

Switching from ON DELETE RESTRICT to ON DELETE CASCADE changes data deletion behavior. Accidentally cascading deletes can wipe out related data you didn't intend to lose.

-- DANGEROUS: Now deleting a user deletes all their orders
ALTER TABLE orders DROP CONSTRAINT fk_orders_user;
ALTER TABLE orders ADD CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

Safe pattern: Document why the cascade is needed. Test on a staging database with representative data. Consider soft deletes (deleted_at) instead of hard deletes with cascades.

8. Removing a Default Value Risk: 60

Applications rely on defaults. Remove one, and INSERTs that don't specify the column start failing or producing unexpected NULLs.

-- DANGEROUS if app relies on implicit default
ALTER TABLE orders ALTER COLUMN status DROP DEFAULT;

Safe pattern: Audit all INSERT statements in your codebase. Update the application to always specify the column before dropping the default.

9. Changing the Primary Key Risk: 95

Primary keys are referenced by foreign keys, ORM relationships, and caching layers. Changing them is one of the most dangerous operations you can perform.

-- DANGEROUS: Breaks all foreign key relationships
ALTER TABLE orders DROP CONSTRAINT orders_pkey;
ALTER TABLE orders ADD PRIMARY KEY (uuid);

Safe pattern: Don't change primary keys in place. Create a new table with the desired structure, migrate data, update foreign keys, and swap tables using a rename transaction.

10. Dropping a Table Risk: 90

The nuclear option. Even if you think nothing references the table, there's always that one reporting script, analytics pipeline, or legacy microservice.

-- DANGEROUS: Irreversible data loss
DROP TABLE legacy_events;

Safe pattern:

1. Rename the table first (ALTER TABLE ... RENAME TO ..._deprecated)
2. Monitor for errors for 1–2 weeks
3. Then drop it

How to Catch These Before They Hit Production

Manually reviewing every migration for these 10 patterns is exhausting and error-prone. That's why we built SchemaLens with automatic breaking-change detection.

When you paste two schemas into SchemaLens, it calculates a Schema Change Risk Score (0–100) based on exactly the patterns above:

• 95+ — Critical risk. Dropped columns, table drops, primary key changes.
• 70–94 — High risk. NOT NULL without defaults, type narrowing, unique constraints on duplicates.
• 40–69 — Medium risk. Index drops, FK cascade changes, default removals.
• 0–39 — Low risk. Adding nullable columns, new tables, safe index additions.

The risk score appears in the visual diff, the migration SQL output, and the CI/CD integration. You can configure your pipeline to fail builds on any migration scoring above 70.

Further Reading

• The Complete SQL Migration Checklist: 12 Steps Before Production
• The Schema Review Checklist Every Engineering Team Needs
• SchemaLens in Your CI/CD Pipeline
• SQL CREATE TABLE Validator