Postgres cascade delete using TypeOrm: update or delete on table "table1" violates foreign key constraint on table "table2"

Question

I'm trying to delete records on a file table which depends on table fileDevice using TypeORM.

Actually I get this error

"update or delete on table "file" violates foreign key constraint "FK_4dcd99898oij89898909c2603" on table "file_device""

Here's how I declare tables:

export class File {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  idFonc: number;

  @Column()
  version: number;

  @Column('varchar', { length: 100 })
  filename: string;

  @Column({ nullable: true })
  lastModified: Date;

  @Column()
  device: boolean;

  @Column({ nullable: false })
  typeId: number;

  @ManyToOne(type => Type)
  @JoinColumn({ referencedColumnName: 'id' })
  type: Type;

  @OneToMany(type => FileDevice, filedevice => filedevice.file)
  fileDevice: Promise<FileDevice[]>;
}

And fileDevice

export class FileDevice {
  @PrimaryGeneratedColumn()
  id: number;

  @ManyToOne(type => File, f => f.fileDevice, {
    nullable: false,
    onDelete: 'CASCADE',
  })
  file: File;

  @Column('varchar', { length: 100 })
  deviceid: string;
}

with TypeORM I delete several files :

this.fileRepository.remove(filesListToDelete);

I get the error mentionned above.

I have tried to add onDelete: 'CASCADE' into the definition of the entity:

@OneToMany(type => FileDevice, filedevice => filedevice.file, {
  onDelete: 'CASCADE',
})
fileDevice: Promise<FileDevice[]>;

But I still have the same error.

Should I delete on table fileDevice firstly and then delete filesList on file table ?

Answer 1

I also had a similar issue, in typeorm version 0.2.45 I have fixed it by adding two lines in the newly generated migration file:

first: drop the constraint:

await queryRunner.query(`ALTER TABLE "FileDevice" DROP CONSTRAINT "FK_4dcd99898oij89898909c2603"`);

and then add the constraint with updated cascade:

await queryRunner.query(`ALTER TABLE "FileDevice" ADD CONSTRAINT "FK_4dcd99898oij89898909c2603" FOREIGN KEY ("fileId") REFERENCES "file"("id") ON DELETE CASCADE ON UPDATE CASCADE`);

when you run this migration, solve the issue.

FYI: This works only if you have properly set up migration, if you are using migration simply run the above query on DB shell or query tool.

Answer 2

In the FileDevice migration file if there is one you need to add this. Note onDelete: 'CASCADE' is much needed in the migrations file.

foreignKeys: [
                {
                    referencedTableName: 'file',
                    referencedColumnNames: ['id'],
                    columnNames: ['file'],
                    onDelete: 'CASCADE',

                }
]