Foreign key constraint »FK_0e4022833a9efc062c01637e552« cannot be implemented - problem with composite primary key?

Admin 1
Viewed 3820

I'm using TypeORM and want to design my database with currently three entities. I created a repository for reproduction

Github reproduction repository

Update

I was able to reproduce it with only two small entities

Very small reproduction repo

more information to this down below

So first of all I have a module entity. This one is a small one because it only stores some basic fields.

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

  // some other fields without references
}

Next I have a graph entity. This one is holding multiple graphNodes. So you can have multiple graphs and each one has its own graphNodes. The graph itself needs to know which graphNode is the first one in the graph. So I tried to setup a foreign key reference to the graphNode entity.

  • there can be multiple graphs
  • each graph has one graphNode as a starting node
  • there are multiple graphNodes per graph

.

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

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

  @ManyToOne(
    () => GraphNode,
    graphNode => graphNode.id,
  )
  @JoinColumn({ name: 'startNodeId' })
  public startNode: GraphNode;
}

The last entity is the graphNodes entity. Each graphNode represents a module in one graph. It also knows about its successors successGraphNodeId and errorGraphNodeId. It's important to node that this entity has a composite primary key because if you want to fetch a graphNode you have to pass in the graph id too. Basically these are the use cases

  • a graphNode represents one module
  • there are multiple graphNodes per graph
  • a graphNode can reference itself as a successor (success/error)
  • a graphNode successor can be null (success/error)
  • a graphNode successor must be a graphNode that already exists in this graph

.

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

  @PrimaryColumn()
  public graphId: string; // composite key https://github.com/typeorm/typeorm/blob/master/sample/sample27-composite-primary-keys/entity/Post.ts

  @ManyToOne(
    () => Graph,
    graph => graph.id,
  )
  @JoinColumn({ name: 'graphId' })
  public graph: Graph;

  @Column()
  public moduleId: string;

  @ManyToOne(
    () => Module,
    module => module.id,
  )
  @JoinColumn({ name: 'moduleId' })
  public module: Module;

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

  @ManyToOne(
    () => GraphNode,
    graphNode => graphNode.id,
  )
  @JoinColumn({ name: 'successGraphNodeId' })
  public successGraphNode?: GraphNode;

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

  @ManyToOne(
    () => GraphNode,
    graphNode => graphNode.id,
  )
  @JoinColumn({ name: 'errorGraphNodeId' })
  public errorGraphNode?: GraphNode;
}

When running the application I'm unfortunately getting this error

QueryFailedError: Foreign key constraint »FK_0e4022833a9efc062c01637e552« cannot be implemented
    at new QueryFailedError (...\references-reproduction\node_modules\typeorm\error\QueryFailedError.js:11:28)
    at Query.callback (...\references-reproduction\node_modules\typeorm\driver\postgres\PostgresQueryRunner.js:176:38)
    at Query.handleError (...\references-reproduction\node_modules\pg\lib\query.js:145:17)
    at Connection.connectedErrorMessageHandler (...\references-reproduction\node_modules\pg\lib\client.js:214:17)
    at Connection.emit (events.js:223:5)
    at Socket.<anonymous> (...\references-reproduction\node_modules\pg\lib\connection.js:134:12)
    at Socket.emit (events.js:223:5)
    at addChunk (_stream_readable.js:309:12)
    at readableAddChunk (_stream_readable.js:290:11)
    at Socket.Readable.push (_stream_readable.js:224:10)

It seems my entity design is not correct. Does someone know what's wrong or missing here? Please let me know if you need more information or if I should update my reproduction repository.

Thanks in advance

Update

I was able to reproduce it with two small entities. There only is a relation between a graph

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

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

  @ManyToOne(
    () => Node,
    node => node.id,
  )
  @JoinColumn({ name: 'startNodeId' })
  public node: Node;
}

and its nodes

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

  @PrimaryColumn()
  public graphId: string;

  @ManyToOne(
    () => Graph,
    graph => graph.id,
  )
  @JoinColumn({ name: 'graphId' })
  public graph: Graph;
}

As mentioned before the problem seems to rely on the composite primary key. I don't want to convert the Node's graphId primary column to a basic column because this would be bad database design...

typeorm postgresql sql
3 Answers

I don't think your problem is on the composite key. For me, your model has several flaws:

  • Both Graph and Node have a ManyToOne towards each other. But from what I gather from your explanation, a Node is only attached to a single Graph, so in Graph, it should actually be a OneToMany representing a list of Nodes:
@OneToMany(() => Node)
public nodes: Node[];
  • The second argument of annotation ManyToOne is supposed to be the reverse-side attribute, yet you used it to reference the primary key instead. Here is what you should have:
class Graph {
  @OneToMany(() => Node, node => node.graph)
  public nodes: Node[];
}

class Node {
  @ManyToOne(() => Graph, graph => graph.nodes)
  public graph: Graph;
}

For the JoinColumn, typeorm should automatically take what you have specified have primary columns in each entity.

user_19805700

You have two primary keys on GraphNode

@PrimaryGeneratedColumn('uuid')
public id: string;

@PrimaryColumn()
public graphId: string;

that's why the fk's on self-referenced success- and errorGraphNode won't work. Remove one PK and work with the other.

user_12141720

You are using foreign key constraint in your entities as:

@ManyToOne(
    () => GraphNode,
    graphNode => graphNode.id,
)

and respectively in the other entity. You are getting the error because your current database doesn't satisfies this rule. So, the simplest way to get this working is to:

  1. Delete current database
  2. Create a new database
  3. Change configuration to connect to new created database.

Doing this will work all the newly created records would be according to the updated constraints in the database. Hope this works for you.

user_89965270
Related