mysql - TypeORM/MySQL：无法删除或更新父行：外键约束失败

Question

comments我在和之间有实体关系posts _many-to-one_。我正在使用typeorm和typegraphql

这是我的帖子实体：

@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;
}

这是我的评论实体：

@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;
}

我对其他突变没有任何问题，唯一的问题是当我想删除帖子时。我在 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`))",
...
}

这是我删除帖子的解析器。

@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;
  }
}

什么可能是我的问题，我已经设置了onDelete，CASCADE所以这里有什么问题？请帮忙。

标签： mysqlnode.jstypescripttypeorm

Answer 1

您需要onDelete在Comment实体中为 column设置post。删除Post实体时，实体中的post列Comment需要解析关系引用，但onDelete没有为post列设置，查询无法运行。

评论实体：

@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;
}