Combining ManyToOne, Cascade, & Composite Primary Key causes Unique Constraint issues #6416
Description
Issue type:
[ ] question
[x] bug report
[ ] feature request
[ ] documentation issue
Database system/driver:
[ ] cordova
[ ] mongodb
[ ] mssql
[ ] mysql / mariadb
[ ] oracle
[ ] postgres
[ ] cockroachdb
[x] sqlite
[ ] sqljs
[ ] react-native
[ ] expo
TypeORM version:
[x] latest
[ ] @next
[ ] 0.x.x (or put your version here)
Steps to reproduce or a small repository showing the problem:
I've found that a relationship between two entities where one is ManyToOne, cascading, and the relationship is part of a composite primary key causes various problems when persisting the entities.
Generally, what happens is that during persistence when the subject recomputes, the ManyToOne primary key field gets set with the entity on the other side of the relationship.
However, when SubjectDatabaseEntityLoader.findByPersistEntityLike gets called it can query the Entity from the database (because { where: { otherId: OtherEntity() } } works but subject.metadata.compareEntities can't understand the relationship.
This means that the first time we save a record and the related entities it works & inserts everything as expected. However, the second time it does not and tries to insert the related entities again - even with their primary keys - causing a unique constraint issue.
In my case, because we're using transformers on the primary keys, I am struggling to get the relationship to work at all without the field.
I'll be creating a test and digging into a few possible solutions for this.
Minimal Example Entities which exhibit the behavior below. To note, PostTag has a custom generation because sqlite does not support composite primary key AUTOINCREMENT.
import { EntitySchema } from '../../../../src/entity-schema/EntitySchema';
import PostTag from './PostTag';
export default class Post {
postId: number;
title: string;
tags: PostTag[];
}
export const PostSchema = new EntitySchema<Post>({
name: 'Post',
target: Post,
columns: {
postId: {
generated: true,
type: Number,
primary: true
}
},
relations: {
tags: {
target: () => PostTag,
type: "one-to-many",
inverseSide: (tag: PostTag) => tag.post,
cascade: true
}
}
});
import { EntitySchema } from '../../../../src/entity-schema/EntitySchema';
import Post from './Post';
let id = 0;
export default class PostTag {
postTagId: number;
postId: string;
name: string;
post: Post;
constructor () {
this.postTagId = id++;
}
}
export const PostTagSchema = new EntitySchema<PostTag>({
name: 'PostTag',
target: PostTag,
columns: {
postId: {
type: Number,
primary: true
},
postTagId: {
type: Number,
primary: true
}
},
relations: {
post: {
nullable: false,
target: () => Post,
inverseSide: (post: Post) => post.tags,
type: 'many-to-one',
joinColumn: { name: 'postId' }
}
}
});
The error received on the second save of the Entity when using SQLite is:
QueryFailedError: SQLITE_CONSTRAINT: UNIQUE constraint failed: post_tag.otherId, post_tag.postId, post_tag.postTagId
at new QueryFailedError (typeorm/src/error/QueryFailedError.ts:9:9)
at Statement.handler (typeorm/src/driver/sqlite/SqliteQueryRunner.ts:53:26) {
errno: 19,
code: 'SQLITE_CONSTRAINT',
query: 'INSERT INTO "post_tag"("otherId", "postId", "postTagId") VALUES (?, ?, ?)',
parameters: [ 3, 2, 1 ]
}