SQL / DEFINING TABLES AND CONSTRAINTS
Changing and dropping tables without breaking dependents
Change or drop a table without leaving broken views and foreign keys behind, by reading dependency errors instead of reaching for CASCADE.
What you will learn
- Read a dependency error's DETAIL line to name the exact object blocking a drop
- Predict what DROP ... CASCADE removes, and treat each NOTICE as a receipt
- Find a table's children with pg_constraint before you change or drop it
- Replace risky renames with add, backfill, drop so outside code keeps working
Understanding Changing and dropping tables without breaking dependents
PostgreSQL records in the catalog table pg_depend every object that leans on your table: the foreign key constraints belonging to child tables, the views and materialized views that read its columns, the sequences and indexes it owns, the generated columns computed from it. DROP TABLE and ALTER TABLE ... DROP COLUMN default to RESTRICT, so the server walks that graph and refuses the change rather than leaving behind an object whose definition no longer makes sense. The refusal is not an obstacle to route around; the DETAIL line of the error is a complete list of the work you owe.
CASCADE does not resolve a dependency, it deletes the dependent object, and that object is frequently not the one you had in mind. Dropping a parent table with CASCADE removes the foreign key constraint that lives on a child table you never mentioned in the statement; the child keeps every one of its rows, and from that moment nothing stops values pointing at a parent that no longer exists. Each removal prints a NOTICE, and those notices are the receipt for the guarantees you just spent.
The dependents the database can see are only the ones written as SQL objects. Application queries, ORM models, dashboards, and dynamic SQL built inside functions hold your table and column names as plain text, and no catalog knows they exist, which is why durable changes are additive and staged: add the new column or table, write to both, move readers across, then drop the old one in a later change. A rename is the trap in this picture, because a view keeps working (it tracked the column by number, not by name) so the database stays quiet while every string of SQL outside it breaks at once.
CREATE TABLE author (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE book (
id integer PRIMARY KEY,
author_id integer NOT NULL REFERENCES author (id),
title text NOT NULL
);
CREATE VIEW book_title AS SELECT id, title FROM book;
-- book's foreign key depends on author, so the drop is refused
DROP TABLE author;
-- the view reads book.title, so that column cannot go either
ALTER TABLE book DROP COLUMN title;
-- adding is always safe: nothing can already depend on a new column
ALTER TABLE book ADD COLUMN subtitle text;A table is a node in a dependency graph, so every change to it must either satisfy its dependents or destroy them.
Worked examples
CASCADE drops the constraint, not the child
Shows that DROP TABLE ... CASCADE leaves the child table full of rows but stripped of its foreign key.
CREATE TABLE team (id integer PRIMARY KEY);
CREATE TABLE player (
id integer PRIMARY KEY,
team_id integer REFERENCES team (id)
);
INSERT INTO team VALUES (1);
INSERT INTO player VALUES (10, 1);
DROP TABLE team CASCADE;
INSERT INTO player VALUES (11, 999);
SELECT id, team_id FROM player ORDER BY id;Example explained
Line 1The NOTICE names one object per cascade, and here that object is player_team_id_fkey, a constraint on a table the statement never mentioned.
Line 2player still has its original row 10, so nothing was deleted except the guarantee attached to team_id.
Line 3The insert of team_id 999 now succeeds, which is the bug CASCADE bought: a reference to a team that never existed.
A rename a view survives
Shows that a dependent view keeps working after a column rename because it stored an attribute number, not the name.
CREATE TABLE product (id integer PRIMARY KEY, price numeric);
CREATE VIEW price_list AS SELECT id, price FROM product;
INSERT INTO product VALUES (1, 9.99);
ALTER TABLE product RENAME COLUMN price TO unit_price;
SELECT * FROM price_list;Example explained
Line 1The rename is accepted with no notice or error, because the view's reference was resolved to a column number when it was created.
Line 2price_list still exposes a column named price: a view's output names are fixed at creation, so SELECT pg_get_viewdef('price_list') now shows unit_price AS price.
Line 3Every consumer that holds the name as text instead (application SQL, EXECUTE strings, saved reports) fails on its next run, and the database gave no warning.
Important notes
ALTER TABLE ... DROP COLUMN in PostgreSQL only marks the column dropped in the catalog; existing rows keep the bytes until they are rewritten, so a dropped column is not a way to erase sensitive data.
The RESTRICT/CASCADE behaviour is not portable: MySQL parses those keywords on DROP TABLE and ignores them, and SQL Server has no CASCADE clause at all, where a view without SCHEMABINDING simply breaks the next time someone queries it.
Common mistakes
Adding CASCADE the moment a drop is refused: the parent goes, the child's foreign key goes with it, and invalid references start entering the child table with nothing reporting it.
Reading DROP TABLE ... CASCADE as ON DELETE CASCADE and expecting child rows to be cleaned up: it deletes the constraint, and the now-orphaned rows stay exactly where they were.
Renaming a column because no view complained: dependent views are quietly rewritten and keep working, while application queries and dynamic SQL break immediately with 'column does not exist'.
Try it yourself
Change, predict, then run
Create a parent table, a child table with a foreign key to it, and a view over the child, then make DROP TABLE parent succeed without ever typing CASCADE, using only the objects named in the error DETAIL. Count how many statements it takes.
Open the SQL workspaceCheck your understanding
You run DROP TABLE customer CASCADE and psql prints: NOTICE: drop cascades to constraint orders_customer_id_fkey on table orders. What is true of orders afterwards?
- It keeps all its rows, and nothing now rejects a customer_id that matches no customer
- It was dropped as well, since CASCADE removes tables that reference the dropped table
- Its rows that referenced customer were deleted, the same way ON DELETE CASCADE works
- It is marked invalid and must be recreated before it can be queried again
Show answer
CASCADE drops the objects that depend on the target, and the dependent object listed in the notice is a constraint, so only the constraint disappeared: the table and its rows remain, unprotected. Option 3 is tempting because the word CASCADE is shared with the referential action, but ON DELETE CASCADE governs what happens to rows during DML, while DROP TABLE ... CASCADE only removes catalog objects.