When attempting to truncate a table in PostgreSQL, you might encounter the error “cannot truncate a table referenced in a foreign key constraint“. This is the default behaviour for the TRUNCATE statement whenever you try to truncate a table that is referenced by another table through a foreign key relationship.

If you want to truncate both tables, you can use the CASCADE option of the TRUNCATE statement. Alternatively, you could truncate both tables at the same time.

Example of Error

In order to demonstrate the error, we’ll create two tables, one with a foreign key constraint that references the other table. We’ll populate both with data:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50)
);

CREATE TABLE employee_projects (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER REFERENCES employees(id),
    project_name VARCHAR(100)
);

INSERT INTO employees (first_name, last_name, department)
VALUES
    ('Mickey', 'Clooney', 'Sales'),
    ('Donald', 'Pitt', 'Marketing'),
    ('Goofy', 'Jolie', 'IT'),
    ('Minnie', 'Aniston', 'HR');

INSERT INTO employee_projects (employee_id, project_name)
VALUES
    (1, 'Project A'),
    (2, 'Project B'),
    (3, 'Project C');

SELECT * FROM employees;
SELECT * FROM employee_projects;

Result:

 id | first_name | last_name | department 
----+------------+-----------+------------
  1 | Mickey     | Clooney   | Sales
  2 | Donald     | Pitt      | Marketing
  3 | Goofy      | Jolie     | IT
  4 | Minnie     | Aniston   | HR
(4 rows)

 id | employee_id | project_name 
----+-------------+--------------
  1 |           1 | Project A
  2 |           2 | Project B
  3 |           3 | Project C

Now let’s try to truncate the employees table using the default options:

TRUNCATE TABLE employees;

Result:

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

The operation failed with an error. The error message suggests that we either truncate both tables at the same time, or use the CASCADE option.

Solution 1

One option is to use the CASCADE option:

TRUNCATE TABLE employees RESTART IDENTITY CASCADE;

Result:

NOTICE:  truncate cascades to table "employee_projects"
TRUNCATE TABLE

The operation succeeded. PostgreSQL informed us that the TRUNCATE operation cascaded to the other table.

In this example I also used the RESTART IDENTITY option, so that the numbering of the id column restarts at 1 when we next insert data. This is optional.

Solution 2

Another way to deal with the issue is to name both tables in our TRUNCATE statement:

TRUNCATE TABLE employees, employee_projects RESTART IDENTITY;

Result:

TRUNCATE TABLE

The operation succeeded without the need to specify CASCADE.