时间戳

首页 > 解决方案 > 外键约束问题

postgresql - 外键约束问题

问题描述

我正在开发一个创建想法 api 的项目，类似于论坛，人们可以在其中发布想法，其他人可以评论、赞成和反对。

我的数据库遇到问题，我不知道如何解决。

错误是："update or delete on table \"idea\" violates foreign key constraint \"FK_861b419cce1c9ae64295300d6b6\" on table \"comment\"

因此，正如你们在错误中看到的那样，我有一个名为“idea”的表格，我在其中放置了想法和评论。

但是，当我尝试删除附有一些评论的 Idea 时，就会发生此错误。

如果我在没有任何评论的情况下删除这个想法，一切都会好起来的。

但是，如果这个想法附有她的评论，就会出现这个错误。

我在网上搜索了一下，有些人对我说要把我的关系@OneToMany（一个想法，很多评论）{ cascade: true }，但没有任何事情发生。

我将发布我的实体是如何组织的。

澄清一下，我使用 NestJS 框架和 PostgreSQL 作为关系数据库


import {
  Entity,
  PrimaryGeneratedColumn,
  CreateDateColumn,
  Column,
  ManyToOne,
  JoinTable,
} from 'typeorm';
import { UserEntity } from 'src/user/user.entity';
import { IdeaEntity } from 'src/idea/idea.entity';

@Entity('comment')
export class CommentEntity {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @CreateDateColumn()
  created: Date;

  @Column('text')
  comment: string;

  @ManyToOne(type => UserEntity)
  @JoinTable()
  author: UserEntity;

  @ManyToOne(
    type => IdeaEntity,
    idea => idea.comments,
  )
  idea: IdeaEntity;
}


import {
  Entity,
  PrimaryGeneratedColumn,
  CreateDateColumn,
  Column,
  ManyToOne,
  UpdateDateColumn,
  ManyToMany,
  JoinTable,
  OneToMany,
} from 'typeorm';
import { UserEntity } from 'src/user/user.entity';
import { CommentEntity } from 'src/comment/comment.entity';

@Entity('idea')
export class IdeaEntity {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @CreateDateColumn()
  created: Date;

  @UpdateDateColumn()
  updated: Date;

  @Column('text')
  idea: string;

  @Column('text')
  description: string;

  @ManyToOne(
    type => UserEntity,
    author => author.ideas,
  )
  author: UserEntity;

  @ManyToMany(type => UserEntity, { cascade: true })
  @JoinTable()
  upvotes: UserEntity[];

  @ManyToMany(type => UserEntity, { cascade: true })
  @JoinTable()
  downvotes: UserEntity[];

  @OneToMany(
    type => CommentEntity,
    comment => comment.idea,
    { cascade: true },
  )
  comments: CommentEntity[];
}

编辑1：我把一切都抓住了，看看更多关于这个错误的信息，这就是我的终端所显示的

error: error: update or delete on table "idea" violates foreign key constraint "FK_861b419cce1c9ae64295300d6b6" on table "comment"
    at Connection.parseE (/home/gabriel/Desktop/Token/ideas-api/node_modules/pg/lib/connection.js:614:13)
    at Connection.parseMessage (/home/gabriel/Desktop/Token/ideas-api/node_modules/pg/lib/connection.js:413:19)
    at Socket.<anonymous> (/home/gabriel/Desktop/Token/ideas-api/node_modules/pg/lib/connection.js:129:22)
    at Socket.emit (events.js:315:20)
    at addChunk (_stream_readable.js:297:12)
    at readableAddChunk (_stream_readable.js:273:9)
    at Socket.Readable.push (_stream_readable.js:214:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:186:23) {
  name: 'error',
  length: 325,
  severity: 'ERROR',
  code: '23503',
  detail: 'Key (id)=(9452b702-e7e8-4585-adf7-ee84d16306cb) is still referenced from table "comment".',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: 'public',
  table: 'comment',
  column: undefined,
  dataType: undefined,
  constraint: 'FK_861b419cce1c9ae64295300d6b6',
  file: 'ri_triggers.c',
  line: '3280',
  routine: 'ri_ReportViolation'
}
[Nest] 25768   - 03/28/2020, 4:28:40 PM   {"code":500,"timestamp":"2020-03-28T19:28:40.221Z","path":"/idea/9452b702-e7e8-4585-adf7-ee84d16306cb","method":"DELETE","message":"update or delete on table \"idea\" violates foreign key constraint \"FK_861b419cce1c9ae64295300d6b6\" on table \"comment\""} +12315ms

标签： postgresqlnestjs

解决方案

您在此处设置的cascade属性用于INSERT和UPDATE操作，如属性文档中所述：

/**
 * Sets cascades options for the given relation.
 * If set to true then it means that related object can be allowed to be inserted or updated in the database.
 * You can separately restrict cascades to insertion or updation using following syntax:
 *
 * cascade: ["insert", "update"] // include or exclude one of them
 */

允许您执行此级联的属性DELETE设置onDelete为CASCADE：

/**
 * Database cascade action on delete.
 */
onDelete?: OnDeleteType;

存在OnDeleteType：

/**
 * ON_DELETE type to be used to specify delete strategy when some relation is being deleted from the database.
 */
export declare type OnDeleteType = "RESTRICT" | "CASCADE" | "SET NULL" | "DEFAULT" | "NO ACTION";

但是，级联删除一直是 TypeORM 中一个乏味的话题（你可以在 GitHub 项目上看到多个问题）。我认为这适用于 ManyToMany，但根据您使用的版本，我不能保证结果......

推荐阅读