Dropping Constraints Safely During the Contract Phase

The expand-and-contract migration is almost done: the new customer_ref column is live, backfilled and read by every service, and the last pull request removes the old customer_id column. It fails in staging with cannot drop column customer_id of table orders because other objects depend on it — a foreign key, a check constraint and a view all reference it. Someone adds CASCADE, the migration passes, and in production the DROP ... CASCADE quietly removes a view that the reporting service queries every minute. Dropping constraints is cheap for the database, but it is not free of consequences: every drop needs a brief exclusive lock that can queue behind long transactions, dependent objects disappear with CASCADE, and a constraint that looked redundant may be the only thing stopping a still-running writer from inserting bad data. This guide sets the order and checks for the contract phase. It belongs to Adding Constraints Without Downtime.

Symptom / Error Signatures

Contract-phase drops go wrong in a few recognisable ways:

ERROR:  cannot drop column customer_id of table orders because other objects depend on it
DETAIL:  constraint orders_customer_fk on table orders depends on column customer_id of table orders
view order_summary depends on column customer_id of table orders
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Or the drop succeeds and something else breaks: a reporting query fails with relation "order_summary" does not exist after CASCADE removed a view; a lock-timeout error (55P03) appears because DROP CONSTRAINT queued behind a long transaction; or bad data starts appearing because a constraint was dropped while an old writer was still running. On MySQL, dropping a column used by a foreign key fails with ERROR 1828 (HY000): Cannot drop column 'customer_id': needed in a foreign key constraint.

Root Cause Analysis

PostgreSQL tracks dependencies between objects in pg_depend: constraints, indexes, views, triggers and generated columns that reference a column all depend on it. Without CASCADE, a drop refuses while dependents exist; with CASCADE, it silently drops every dependent, recursively. That is convenient in development and dangerous in production, because the migration author may not know that a view created by another team exists.

The locks are small but real. DROP CONSTRAINT and DROP COLUMN take ACCESS EXCLUSIVE on the table briefly (a foreign-key drop also locks the referenced table), so each can queue behind long transactions and make new queries queue behind it — the lock-queue pathology from DDL Lock Management & Timeouts. And constraints encode guarantees: dropping a NOT NULL or check while an older version of a writer is still deployed removes the protection exactly when it might be needed.

Immediate Mitigation

1. List dependents before writing the drop.

-- PostgreSQL · read-only · objects that depend on orders.customer_id
SELECT classid::regclass AS catalog, objid, deptype,
       pg_describe_object(classid, objid, objsubid) AS dependent
FROM pg_depend
WHERE refobjid = 'orders'::regclass
  AND refobjsubid = (SELECT attnum FROM pg_attribute
                     WHERE attrelid = 'orders'::regclass AND attname = 'customer_id');

2. Resolve each dependent deliberately. Recreate views against the new column (with CREATE OR REPLACE VIEW, keeping the same output columns) before the drop; drop indexes concurrently; drop constraints by name.

-- PostgreSQL · migration role · each statement with a lock timeout
-- WARNING: the view must keep its column names and types for CREATE OR REPLACE to succeed.
SET lock_timeout = '3s';
CREATE OR REPLACE VIEW order_summary AS
  SELECT o.id, o.customer_ref AS customer_id, o.total FROM orders o;
ALTER TABLE orders DROP CONSTRAINT IF EXISTS orders_customer_fk;
ALTER TABLE orders DROP CONSTRAINT IF EXISTS orders_customer_positive;
-- ROLLBACK PATH: re-add constraints NOT VALID and VALIDATE; recreate the previous view definition.

-- PostgreSQL · outside a transaction · removes the index without blocking writes
SET lock_timeout = '3s';
DROP INDEX CONCURRENTLY IF EXISTS orders_customer_id_idx;

3. Drop the column without CASCADE. If anything still depends on it, the drop fails and tells you — which is exactly what you want.

-- PostgreSQL · migration role · fails safely if an unexpected dependent exists
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders DROP COLUMN customer_id;
COMMIT;

4. Retry on lock timeout, not with a longer timeout. If the drop times out, a long transaction is holding the table; wait or clear it, per clearing idle-in-transaction sessions before a migration, then retry.

Permanent Fix / Long-Term Pattern

Ban CASCADE from production migrations through linting, so every dependent object removed is named in a reviewed statement. Make the dependency query part of the contract-phase checklist, alongside the evidence that no deployed code uses the old structure — query statistics from pg_stat_statements and a code search across every service, as in safely removing a NOT NULL column with expand-contract. Order the drops from the outside in: views and other dependents, then indexes (concurrently), then constraints, then the column.

Think about guarantees before convenience. A constraint on the old column protects data written by old code; drop it only once every writer has moved to the new column. And if the new column should carry equivalent guarantees, add them first — NOT VALID then VALIDATE, as in adding foreign keys with NOT VALID and VALIDATE CONSTRAINT — so there is never a window with no protection at all.

Verification Checklist

• pg_stat_statements and service code searches show no use of the old column or constraint.
• The dependency query was run and every dependent has an explicit statement in the migration.
• No production migration uses CASCADE.
• Each drop ran with lock_timeout, and indexes were dropped concurrently.
• Equivalent guarantees on the new column were validated before the old constraints were removed.

Frequently Asked Questions

Is DROP CONSTRAINT expensive? The work is trivial — a catalog update — but it needs a brief ACCESS EXCLUSIVE lock (plus a lock on the referenced table for foreign keys). The risk is queueing behind long transactions, so use a lock timeout and retry.

Why avoid CASCADE? Because it drops every dependent object, recursively, including views, constraints and triggers other teams may rely on. Naming each dependent in the migration makes the impact visible in review.

Should I drop the foreign key before or after the column? Before. Dropping it explicitly lets you control the lock timeout and makes the column drop fail loudly if some other dependent was missed. Dropping the column without CASCADE while a key exists fails anyway.

How do I drop constraints in MySQL? Use ALTER TABLE ... DROP FOREIGN KEY name, DROP CHECK name or DROP INDEX name; these are metadata or in-place operations in MySQL 8.0. MySQL also refuses to drop a column used by a foreign key until the key is removed, so the same outside-in order applies.