TypeORM/MySQL: Cannot delete or update a parent row: a foreign key constraint fails

Question

I have an entity relationship between comments and posts _many-to-one_. I'm using typeorm and typegraphql

Here is my post entity:

@ObjectType()
@Entity()
export class Post extends BaseEntity {
  constructor(input: InputI) {
    super();
    this.caption = input?.caption;
    this.imageURL = input?.imageURL;
    this.status = input?.status;
    this.user = input?.user;
  }

  @Field(() => Int)
  @PrimaryGeneratedColumn({ type: "int" })
  id: number;

  @Field(() => String, { nullable: true })
  @Column({ type: "text", nullable: true })
  caption?: string;

  @Field(() => String, { nullable: true })
  @Column({ type: "text", nullable: true })
  imageURL?: string;

  @Field(() => String, { nullable: true })
  @Column({ type: "text", nullable: true })
  status?: string;

  // Relations
  @Field(() => User)
  @ManyToOne(() => User, (user) => user.posts)
  user: User;

  @Field(() => [Comments], { nullable: true })
  @OneToMany(() => Comments, (comment) => comment.post, {
    onDelete: "CASCADE",
    onUpdate: "CASCADE",
  })
  comments: Comments[];

  @Field(() => [Likes], { nullable: true })
  @OneToMany(() => Likes, (like) => like.post, {
    onDelete: "CASCADE",
    onUpdate: "CASCADE",
  })
  likes: Likes[];

  @Field(() => String)
  @CreateDateColumn({ nullable: false })
  createdAt: Date;
  
  @Field(() => String)
  @UpdateDateColumn({ nullable: false })
  updatedAt: Date;
}

Here is my Comment Entity:

@ObjectType()
@Entity()
export class Comments extends BaseEntity {
  @Field(() => Int)
  @PrimaryGeneratedColumn({ type: "int" })
  id: number;

  @Field(() => String)
  @Column({ nullable: true })
  avatar: string;

  @Field(() => String)
  @Column({ nullable: false })
  comment: string;

  @Field(() => String)
  @Column({ nullable: false })
  email: string;

  @Field(() => String)
  @Column({ nullable: false })
  username: string;

  @Field(() => String)
  @Column({ nullable: true })
  phoneNumber: string;

  @Field(() => String)
  @Column({ nullable: false })
  gender: string;

  @Field(() => String)
  @Column({ nullable: false })
  status: string;

  @Field(() => String)
  @Column({ nullable: true, type: "text" })
  bio: string;

  @Field(() => Boolean)
  @Column({ nullable: false, default: false })
  verified: false | true;
  // @Field(() => [Comments], { nullable: true })
  // @OneToMany(() => Comments, (comment) => comment)
  // @JoinColumn()
  // replies: Comments[];
  //  @ManyToOne(() => Post, (post) => post.likes)
  // post: Post;

  @ManyToOne(() => Post, (post) => post.comments)
  post: Post;
  //
  @Field(() => String)
  @CreateDateColumn({ type: "datetime", nullable: false })
  createdAt: Date;

  @Field(() => String)
  @UpdateDateColumn({ type: "datetime", nullable: false })
  updatedAt: Date;
}

I don't have any problem with other mutation, the only problem comes when i want to delete a post. I'm getting the following error in the GraphQLPlayGround:

"errors": [
    {
      "message": "Cannot delete or update a parent row: a foreign key constraint fails (`likeme`.`comments`, CONSTRAINT `FK_e44ddaaa6d058cb4092f83ad61f` FOREIGN KEY (`postId`) REFERENCES `post` (`id`))",
...
}

Here is my resolver that delete a posts.

@Resolver()
export class DeletePostResolver {
  @Mutation(() => Boolean)
  async deletePost(
    @Arg("id", () => Int) id: number,
    @Ctx() { req }: UserContext
  ): Promise<Boolean> {
    const post = await Post.findOne(
      { id },
      { relations: ["user", "comments", "likes"] }
    );
    if (post?.user.id === req.session.userId) {
      await Post.delete({ id });
      return true;
    }
    return false;
  }
}

What maybe possibly my problem, I've set the onDelete to CASCADE so what's wrong here? Help please.

Answer 1

You need to set onDelete in Comment entity for column post. when you delete a Post entity, post column in Comment entity need to resolve relation reference, but onDelete is not set for post column then, query cannot run.

Comment Entity:

@ObjectType()
@Entity()
export class Comments extends BaseEntity {
  @Field(() => Int)
  @PrimaryGeneratedColumn({ type: "int" })
  id: number;

  @Field(() => String)
  @Column({ nullable: true })
  avatar: string;

  @Field(() => String)
  @Column({ nullable: false })
  comment: string;

  @Field(() => String)
  @Column({ nullable: false })
  email: string;

  @Field(() => String)
  @Column({ nullable: false })
  username: string;

  @Field(() => String)
  @Column({ nullable: true })
  phoneNumber: string;

  @Field(() => String)
  @Column({ nullable: false })
  gender: string;

  @Field(() => String)
  @Column({ nullable: false })
  status: string;

  @Field(() => String)
  @Column({ nullable: true, type: "text" })
  bio: string;

  @Field(() => Boolean)
  @Column({ nullable: false, default: false })
  verified: false | true;
  // @Field(() => [Comments], { nullable: true })
  // @OneToMany(() => Comments, (comment) => comment)
  // @JoinColumn()
  // replies: Comments[];
  //  @ManyToOne(() => Post, (post) => post.likes)
  // post: Post;

  @ManyToOne(() => Post, (post) => post.comments, {
    onDelete: "CASCADE", // <---- HERE
  })
  post: Post;
  //
  @Field(() => String)
  @CreateDateColumn({ type: "datetime", nullable: false })
  createdAt: Date;

  @Field(() => String)
  @UpdateDateColumn({ type: "datetime", nullable: false })
  updatedAt: Date;
}