QueryFailedError: insert or update on table "graph" violates foreign key constraint "FK_0e40......"

I created a very small reproduction repository.

Please also have a look at my possible fix down below.

I have a graph and a graph node entity. The graph only knows about its start graph node and the node itself knows about which graph it belongs to and its successor.

@Entity()
export class Graph extends BaseEntity {
  @PrimaryGeneratedColumn("uuid")
  public id: string;

  @Column({ nullable: true })
  public startNodeId?: string;

  @ManyToOne(() => GraphNode)
  @JoinColumn({ name: "startNodeId" })
  public startNode?: GraphNode;
}

@Entity()
export class GraphNode extends BaseEntity {
  @PrimaryGeneratedColumn("uuid")
  public id: string;

  @PrimaryColumn("uuid")
  public graphId: string;

  @ManyToOne(() => Graph, { onDelete: "CASCADE" })
  @JoinColumn({ name: "graphId" })
  public graph: Graph;

  @Column({ nullable: true })
  public successorGraphNodeId?: string;

  @ManyToOne(() => GraphNode)
  @JoinColumn({ name: "successorGraphNodeId" })
  public successorGraphNode?: GraphNode;
}

As you can see the graph's startnode might be null. Further the graph node might have no successor, so this field might be null too. It's important that a graph node has a composite primary key, because it always belongs to one graph.

I created some very basic CRUD repository actions to create a graph and a graph node. Later on I want to update the start node id of that graph.

The graph nodes repository just creates a new entity

  public createGraphNode(graphId: string, successorGraphNodeId?: string): Promise<GraphNode> {
    const graphNode: GraphNode = new GraphNode();
    graphNode.graphId = graphId;
    graphNode.successorGraphNodeId = successorGraphNodeId;
    return graphNode.save();
  }

The graphs repository creates a new entity and is able to update its start node id

  public createGraph(startNodeId?: string): Promise<Graph> {
    const graph: Graph = new Graph();
    graph.startNodeId = startNodeId;
    return graph.save();
  }

  public updateStartNodeId(graphId: string, startNodeId: string): Promise<UpdateResult> {
    return this.update(graphId, {
      startNodeId
    });
  }

When running the application I create a new graph. After that I create a new graph node and assign the graph id to it. This graph node has no successor. Last I assign the graph node as the graph start node.

const graph: Graph = await graphsRepository.createGraph();
const graphNode: GraphNode = await graphNodesRepository.createGraphNode(graph.id);
await graphsRepository.updateStartNodeId(graph.id, graphNode.id);

By doing so I'm getting the following database error

QueryFailedError: insert or update on table "graph" violates foreign key constraint "FK_0e4022833a9efc062c01637e552"

It seems my entity design is wrong. Does someone know how to fix it?

Thanks in advance

I might have a fix for it

Graph entity

Create a reference to the PK via referencedColumnName: "id"

@ManyToOne(() => GraphNode)
@JoinColumn({ name: "startNodeId", referencedColumnName: "id" })
public startNode?: GraphNode;

Graph node entity

Do the same for the successor graph node

@ManyToOne(() => GraphNode)
@JoinColumn({ name: "successorGraphNodeId", referencedColumnName: "id" })
public successorGraphNode?: GraphNode;

Also add the Unique decorator to the entity and mark the id field as unique

@Unique(["id"])

The only problem I have is that I don't know why I have to solve it this way.

• I don't know why I have to reference the PK of the referenced table via referencedColumnName: "id", this should be obvious
• I also don't know why I have to mark the id column as unique via @Unique(["id"]) because this field will always be unique