A clean database is a good database, right? Right!

We'll if that's the case, you certainly want to CASCADE your DROP TABLE so you clean up orphaned objects. But then....oh no! You've deleted everything! How does this happen?

Lets start with a very simple users table.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Now these users can have posts (like a blog post):

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    CONSTRAINT fk_posts_user_id
    FOREIGN KEY (user_id)
    REFERENCES users(id)
);

Great, looking good! Users have blog posts and our DB knows that user_id belongs to, and must exist in the users table. The last three lines are what makes this magic work. Its saying, verify that each post has a valid owner.

REFERENCES helps us avoid errors where we're tying to load a post but the user doesn't exist.

Now, let's accidentally delete everything!

DROP TABLE users CASCADE;

💥 everything is gone. WHY?

CASCADE tells Postgres to delete all related objects. Since posts has REFERENCES users(id), Postgres knows that posts can't exist without users, so the DROP TABLE cascades down to posts.

If you want to drop users and preserve posts, you need to remove the constraint on the user_id column before dropping the table:

ALTER TABLE posts DROP CONSTRAINT posts_user_id_fkey;

And then you can drop the table safely like you did before.

DROP TABLE Best Practice

Never run DROP TABLE with CASCADE without first running it without the CASCADE option. Running it without the CASCADE option will tell you which foreign key constraints you need to remove in order to avoid data loss.