When working with PostgreSQL databases, you may sometimes need to clear out all the data from tables that have foreign key relationships. This process, known as truncation, can be tricky when dealing with interconnected tables.

By default, if we try to truncate a table that is referenced by another table via a foreign key constraint, we’ll get an error that looks something like this: “ERROR: cannot truncate a table referenced in a foreign key constraint“.

You may have encountered this before finding this article. However, all is not lost. Below are two options for overcoming this issue.

The Options

Here are both options in a nutshell:

1. Truncate all tables at once (the primary key table and any/all foreign key tables)
2. Use the CASCADE Option

Examples below.

Sample Data

First, let’s set up a sample database:

-- Create tables
CREATE TABLE cartoon_characters (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    species VARCHAR(50)
);

CREATE TABLE superpowers (
    id SERIAL PRIMARY KEY,
    character_id INTEGER REFERENCES cartoon_characters(id),
    power VARCHAR(100)
);

CREATE TABLE sidekicks (
    id SERIAL PRIMARY KEY,
    character_id INTEGER REFERENCES cartoon_characters(id),
    name VARCHAR(100)
);

-- Insert sample data
INSERT INTO cartoon_characters (name, species)
VALUES 
    ('SpongeBob SquarePants', 'Sea Sponge'),
    ('Mickey Mouse', 'Mouse'),
    ('Bugs Bunny', 'Rabbit');

INSERT INTO superpowers (character_id, power)
VALUES 
    (1, 'Shape-shifting'),
    (2, 'Toon force'),
    (3, 'Fourth wall breaking');

INSERT INTO sidekicks (character_id, name)
VALUES 
    (1, 'Patrick Star'),
    (2, 'Goofy'),
    (3, 'Daffy Duck');

Here, we have a primary key table (cartoon_characters) and two foreign key tables (superpowers and sidekicks).

Now that we’ve created our tables, let’s look at two options for truncating these tables.

Truncate All Tables

If we include all of the related tables in our TRUNCATE statement, PostgreSQL will allow them to be truncated.

TRUNCATE TABLE cartoon_characters, superpowers, sidekicks;

Output:

TRUNCATE TABLE

The output indicates that the operation was successful.

Use the CASCADE Option

PostgreSQL provides us with the CASCADE option to deal with this specific scenario (the default is RESTRICT, which prevents the truncation from occurring whenever there are other tables with a foreign key to the table we’re trying to truncate).

Anyway, when we use the CASCADE option, PostgreSQL goes ahead and truncates any foreign key tables at the same time:

TRUNCATE TABLE cartoon_characters CASCADE;

Output:

NOTICE:  truncate cascades to table "superpowers"
NOTICE:  truncate cascades to table "sidekicks"
TRUNCATE TABLE

We can see from the output that all three tables were truncated.

In case you’re wondering, here’s what happens when we omit the CASCADE argument:

TRUNCATE TABLE cartoon_characters;

Output:

ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "superpowers" references "cartoon_characters".
HINT:  Truncate table "superpowers" at the same time, or use TRUNCATE ... CASCADE.

The error message tells us exactly what happened and provides a suggestion for fixing the problem (which are the same options we used above).