UNIQUE constraint violation when updating

Question

i'm updating table with this schema(sequelize typescript) on mysql

@Table
class ItemImage extends Model<ItemImageCreationAttribute> {
  @Index({
    name: "unq-order-idx",
    type: "UNIQUE",
  })
  @ForeignKey(() => Item)
  @Column({
    type: DataType.UUID,
    primaryKey: true,
  })
  itemId!: string;

  @Column({
    type: DataType.STRING,
    primaryKey: true,
  })
  imageName!: string;

  @Index({
    name: "unq-order-idx",
    type: "UNIQUE",
  })
  @Column({
    type: DataType.INTEGER,
    allowNull: false,
  })
  order!: number;

  @BelongsTo(() => Item)
  parentItem!: Item;
}

i'm updating using sequelize update with transaction, i've also tried using bulkCreate with updateOnDuplicate

const item: Item = (req as any)[Item.name];
const sortedImages = [...item.images].sort((a, b) => a.order - b.order);

// //construct changes
// const changesArray = sortedImages.map((image, i) => ({
//   itemId: item.id,
//   imageName: image.imageName,
//   order: newOrder[i],
// }));

await sequelize.transaction(async (transaction) => {
  await Promise.all(
    sortedImages.map((image, i) =>
      image.update({ order: newOrder[i] }, { transaction })
    )
  );
  // await ItemImage.bulkCreate(changesArray, {
  //   updateOnDuplicate: ["order"],
  //   transaction,
  // });
});

both result in duplicate entry error like this

original: Error: Duplicate entry '519a6070-af42-48db-a3fc-ec434b0e35f3-5' for key 'itemimages.unq-order-idx'

        code: 'ER_DUP_ENTRY',
        errno: 1062,
        sqlState: '23000',
        sqlMessage: "Duplicate entry '519a6070-af42-48db-a3fc-ec434b0e35f3-5' for key 'itemimages.unq-order-idx'",
        sql: 'UPDATE `ItemImages` SET `order`=?,`updatedAt`=? WHERE `itemId` = ? AND `imageName` = ?',
        parameters: [
          5,
          '2023-12-01 07:59:28',
          '519a6070-af42-48db-a3fc-ec434b0e35f3',
          'image0.webp'
        ]
      },

i tried checking the images and the replacement order, there's no problem with both, image names and replacement orders are unique, have the same length, and all images for an item are updated at once.

the error no longer occurs and tests pass(no duplicates) once i remove the unique index.

Answer 1

I ended up changing order values to negative before changing them to the intended value. Please tell me if there's a better or simpler way.

await sequelize.transaction(async (transaction) => {
        await Promise.all(
          sortedImages.map((image, i) =>
            image.update({ order: -image.order }, { transaction })
          )
        );

        await Promise.all(
          sortedImages.map((image, i) =>
            image.update({ order: newOrder[i] }, { transaction })
          )
        );
      });