typeorm: ManyToMany relations save throws "Violation of PRIMARY KEY constraint" when a property from combined primary key from relation entity is an other relation property

Issue Description

While saving an entity with ManyToMany relation the first save works great. But if I change the ManyToMany relation children, the exception Violation of PRIMARY KEY constraint 'PK_c83e17466322282074e0360a96e'. Cannot insert duplicate key in object 'dbo.city_zips_zip'. The duplicate key value is (de, 60311, 41a3433e-f36b-1410-84db-00668d135ea6) throws.

For testing I remove the constraint in the relation table…

In the query log I can see, that you first insert all relations and after inserting you remove all relations. I guess the order is wrong.

INSERT INTO "city_zips_zip"("zipCountryCode", "zipCode", "cityId") VALUES (@0, @1, @2), (@3, @4, @5) -- PARAMETERS: [{"value":"de","type":"nvarchar","params":["2"]},{"value":"60311","type":"nvarchar","params":[]},{"value":"41A3433E-F36B-1410-84DB-00668D135EA6","type":"uniqueidentifier","params":[]},{"value":"de","type":"nvarchar","params":["2"]},{"value":"35781","type":"nvarchar","params":[]},{"value":"41A3433E-F36B-1410-84DB-00668D135EA6","type":"uniqueidentifier","params":[]}]

DELETE FROM "city_zips_zip" WHERE (("cityId" = @0 AND "zipCountryCode" = @1 AND "zipCode" = @2) OR ("cityId" = @3 AND "zipCountryCode" = @4 AND "zipCode" = @5)) -- PARAMETERS: ["41A3433E-F36B-1410-84DB-00668D135EA6","de","35781","41A3433E-F36B-1410-84DB-00668D135EA6","de","60311"]

Expected Behavior

Excepted on sync for ManyToMany I want to only remove relations they aren’t in the new array and only insert new relations.

Actual Behavior

At the moment the save method throws an exception, that an insert in relation table can’t execute because a violation on primary key constraint because you insert before delete all relations.

Steps to Reproduce

Create 2 entities:

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

  @Column()
  caption: string;

  @RelationId('zips')
  zipCodes: string[];

  @ManyToMany(() => Zip, zip => zip.cities, {
    cascade: true,
  })
  @JoinTable()
  zips: Zip[];
}

Entity()
export class Country {
  @PrimaryColumn({ length: 2 })
  code: string;

  @Column()
  caption: string;

  @OneToMany(() => Zip, zip => zip.country)
  zips: Zip[];
}

@Entity()
export class Zip {
  @RelationId('country')
  @PrimaryColumn({ length: 2 })
  countryCode: string;

  @ManyToOne(() => Country, country => country.zips, {
      createForeignKeyConstraints: true,
  })
  @JoinColumn({ name: 'countryCode' })
  country: Country;

  @PrimaryColumn()
  code: string;

  @RelationId('cities')
  cityIds: string[];

  @ManyToMany(() => City, city => city.zips)
  @JoinTable({ name: 'city_zips_zip' })
  cities: City[];
}

Execute following:

await connection.getRepository(Country).save({
  code: 'de',
  caption: 'Germany',
});

// create works great 
const city = await connection.getRepository(City).save({
  id: 'INSERT_ID_OF_TEST_CITY_HERE',
  caption: 'Test city',
  zips: [
    {
      countryCode: 'de',
      code: '60311',
    },
    {
      countryCode: 'de',
      code: '35781',
    },
  ],
}, {
  reload: true,
});

// but if we update the relations the exception throws

await connection.getRepository(City).save({
  id: city.id,
  zips: [
    {
      countryCode: 'de',
      code: '60311',
    },
  ],
});

My Environment

Dependency	Version
Operating System	Linux in Docker
Node.js version	v14.19.1
Typescript version	4.3.5
TypeORM version	0.3.6

Relevant Database Driver(s)

DB Type	Reproducible
aurora-mysql	no
aurora-postgres	no
better-sqlite3	no
cockroachdb	no
cordova	no
expo	no
mongodb	no
mysql	yes
nativescript	no
oracle	no
postgres	no
react-native	no
sap	no
spanner	no
sqlite	yes
sqlite-abstract	no
sqljs	no
sqlserver	yes

Are you willing to resolve this issue by submitting a Pull Request?

• ✖️ Yes, I have the time, and I know how to start.
• ✅ Yes, I have the time, but I don’t know how to start. I would need guidance.
• ✖️ No, I don’t have the time, but I can support (using donations) development.
• ✖️ No, I don’t have the time and I’m okay to wait for the community / maintainers to resolve this issue.