Nothing pulls me out of the “zone” quicker than attempting to drop a table, only to be told “Nope, that table has a dependency!”.

Granted, this usually only happens in my development environments, as I rarely have any need to drop tables in a production environment.

And that’s probably why it “pulls me out of the zone”. I suddenly have to stop and start thinking about what tables contain foreign keys to the one I’m trying to drop. In dev environments, we’re often trying things out and so it’s not out of the question that we might inadvertently try to drop a table without realising it has dependent objects.

Fortunately, when we’re working with PostgreSQL, we have a quick and easy method to overcome this barrier.

The CASCADE Option

The syntax for dropping a table in PostgreSQL goes like this:

DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

So this allows for a few options, including the CASCADE option.

We have the option of specifying either CASCADE or RESTRICT. The default is RESTRICT, so if we don’t specify anything, that’s what will be used.

When RESTRICT is used, the operation will fail if we try to drop a table that is referenced by a view or a foreign key constraint of another table.

So the key to dropping a table that’s referenced by another table is to use the CASCADE option. When we use this option, the foreign key will be dropped from the other table, but not the other table itself (it will remain, but without the foreign key).

If we also want to drop the other table, then we can include that in the list of tables in our DROP TABLE statement.

Sample Data

Suppose we create the following two tables:

CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INTEGER REFERENCES authors(author_id)
);

Here, the books table has a foreign key that references the authors table. Therefore, we could run into trouble if we try to drop the authors table without realising that it’s being referenced by another table.

Attempting to Drop… but Failing

Let’s first try dropping authors without including any extra arguments:

DROP TABLE authors;

Output:

ERROR:  cannot drop table authors because other objects depend on it
DETAIL:  constraint books_author_id_fkey on table books depends on table authors
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

We got an error, as expected.

Fortunately the error message provides us with a suggestion of how to address the issue.

Before we get around to fixing the issue, let’s try again, but this time we’ll explicitly use the RESTRICT keyword:

DROP TABLE authors RESTRICT;

Output:

ERROR:  cannot drop table authors because other objects depend on it
DETAIL:  constraint books_author_id_fkey on table books depends on table authors
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Once again we get an error. And again, this is completely expected. RESTRICT is the default option, so when we omitted RESTRICT in the first example, it went right ahead and used RESTRICT (i.e. prevented us from dropping anything).

Dropping Successfully… BUT…

OK now that we’ve got that out of the way, let’s now try the CASCADE option:

DROP TABLE authors CASCADE;

Output:

DROP TABLE

This output indicates that the table was successfully dropped.

Here’s what happens if we try to select data from the table we just dropped:

SELECT * FROM authors;

Output:

ERROR:  relation "authors" does not exist
LINE 1: SELECT * FROM authors;
                      ^

The CASCADE option also dropped the foreign key from the books table in order to effect this dropping of the authors table. But the books table remains in place:

SELECT * FROM books;

Output:

 book_id | title | author_id 
---------+-------+-----------
(0 rows)

Now, you may be thinking “but I wanted the books table to be dropped too!”, and that’s a fair comment. If we’re trying to drop the parent of a relationship, there’s a high likelihood that we also want to drop the child.

So to drop both the parent and child of the relationship, simply include the child in the list of tables to be dropped:

DROP TABLE IF EXISTS authors, books CASCADE;

Output:

NOTICE:  table "authors" does not exist, skipping
DROP TABLE

In this case I’d already dropped the authors table (and the foreign key) and so PostgreSQL notified me about that.

The Final Drop… Success!

For the sake of completeness, let’s rebuild the tables and foreign key constraint, and then drop them all at once.

Create the tables/foreign key:

CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INTEGER REFERENCES authors(author_id)
);

Now drop everything:

DROP TABLE IF EXISTS authors, books CASCADE;

Output:

DROP TABLE

This time everything was dropped with a single line of code.