By default, you cannot TRUNCATE (empty) a table that has foreign key constraints applied on it. This is to keep the data consistent over multiple tables that are linked by constraints. Nevertheless, it might be necessary to truncate all data from a table.

Here are a few options you can consider to resolve this issue:

Solution 1 – Disable Foreign Key Checks

The fastest way to clean the table is to disable FOREIGN_KEY_CHECKS. Temporarily disabling foreign key checks allows you to truncate the table without encountering the error. However, this should be done with caution as it can lead to data integrity issues if not handled properly.

SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE TABLE `table1`;
TRUNCATE TABLE `table2`;
SET FOREIGN_KEY_CHECKS = 1;

Solution 2 – Delete Rows Instead of Truncating

Instead of truncating the table, you can delete rows from it. Deleting rows one by one will trigger the foreign key constraint checks, allowing MySQL to maintain referential integrity. You can use the DELETE statement with a WHERE clause to remove specific rows.

DELETE FROM your_table WHERE condition;

Solution 3 – Drop and Recreate Foreign Key Constraints

If it’s acceptable for your application, you can drop the foreign key constraints referencing the table you want to truncate, truncate the table, and then recreate the foreign key constraints.

ALTER TABLE referencing_table DROP FOREIGN KEY constraint_name;
TRUNCATE TABLE your_table;
ALTER TABLE referencing_table ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES your_table(column_name);

Photo by Jan Antonin Kolar on Unsplash

Issue type:

[x] question
[x] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[ ] mysql / mariadb
[ ] oracle
[x] postgres
[x] sqlite
[ ] sqljs
[ ] websql

TypeORM version:

[x] latest
[ ] @next
[ ] 0.x.x (or put your version here)

Steps to reproduce or a small repository showing the problem:

I have simple OneToOne relation like this:

@Entity({name: 'user'})
export class User extends BaseEntity {

  @PrimaryGeneratedColumn()
  id: number;

  @Column({length: '50', nullable: true})
  firstName: string;

  @Column({length: '50', nullable: true})
  lastName: string;

}

and:

@Entity({name: 'account'})
export class Account extends BaseEntity {

  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  balance: number;

  @OneToOne(type => User, {})
  @JoinColumn()
  user: User;
}

Delete all users and create new one with following code:

createConnection().then(async (connection) => {
  console.log('Inserting a new user into the database...');
  await connection.getRepository(User).clear(); // remove all users 
  const user = new User();
  user.email = 'admin@admin.com';
  user.role = Role.BORROWER;
  user.password = 'admin';
  user.idnp = '2342332342300';
  await connection.manager.save(user);
  console.log('Saved a new user with id: ' + user.id);

  console.log('Loading users from the database...');
  const users = await connection.manager.find(User);
  console.log('Loaded users: ', users);

  console.log('Here you can setup and run express/koa/any other framework.');

  debug('postgres database connected successfully...');
}).catch((err) => error(err));

Witch produces the following error (error with postgres, with sqlite this error is not pesent.):

_
Inserting a new user into the database…
executing query: TRUNCATE TABLE «user»
query failed: TRUNCATE TABLE «user»
error: { error: cannot truncate a table referenced in a foreign key constraint
at Connection.parseE { … }backend\node_modules\pg\lib\connection.js:113:22)
at Socket.emit (events.js:160:13)
at addChunk (_stream_readable.js:269:12)
at readableAddChunk (_stream_readable.js:256:11)
at Socket.Readable.push (_stream_readable.js:213:10)
at TCP.onread (net.js:602:20)
name: ‘error’,
length: 238,
severity: ‘ERROR’,
code: ‘0A000’,
detail: ‘Table «account» references «user».’,
hint: ‘Truncate table «account» at the same time, or use TRUNCATE … CASCADE.’,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: ‘heap.c’,
line: ‘2983’,
routine: ‘heap_truncate_check_FKs’ }
error: { QueryFailedError: cannot truncate a table referenced in a foreign key constraint
….
backend\node_modules\pg\lib\connection.js:117:12)
at Socket.emit (events.js:160:13)
at addChunk (_stream_readable.js:269:12)
at readableAddChunk (_stream_readable.js:256:11)
at Socket.Readable.push (_stream_readable.js:213:10)
message: ‘cannot truncate a table referenced in a foreign key constraint’,
name: ‘QueryFailedError’,
length: 238,
severity: ‘ERROR’,
code: ‘0A000’,
detail: ‘Table «account» references «user».’,
hint: ‘Truncate table «account» at the same time, or use TRUNCATE … CASCADE.’,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: ‘heap.c’,
line: ‘2983’,
routine: ‘heap_truncate_check_FKs’,
query: ‘TRUNCATE TABLE «user»‘,
parameters: [] }
internal/child_process.js:409
throw errnoException(err, ‘kill’);
^
_

Though it is a rare occurrence, we have had occasions where we need to purge ALL data from a table. Our preferred is the TRUNCATE TABLE approach because it’s orders of magnitude faster than the DELETE FROM construct. You however can’t use TRUNCATE TABLE unqualified, if the table you are truncating has foreign key references from other tables.
In comes its extended form, the TRUNCATE TABLE .. CASCADE construct which was introduced in PostgreSQL 8.2, which will not only delete all data from the main table, but will CASCADE to all the referenced tables.

TRUNCATE TABLE orders;

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

TUNCATE TABLE orders CASCADE;

NOTICE:  truncate cascades to table "order_items"