Having unique constraint error while trying to add a second record through association table

Question

I'm working with nodejs, sequelize and mysql. I have 3 tables, user, participe and tirage and I defined my association like this :

db.users.belongsToMany(db.tirages, {through: db.participe, foreignKey:'participantId', as: 'participatingTiragesParticipant'});

db.users.belongsToMany(db.tirages, {through: db.participe, foreignKey:'cadeauA', as: 'CadeauTirage'});

db.tirages.belongsToMany(db.users, {through: db.participe, foreignKey:'tirageUuid', as: 'participatingTiragesTirage'})

Here is the model of participe table :

module.exports = (sequelize, Sequelize) => {
    const Participe = sequelize.define("participe", {
        uuid: {
            type: Sequelize.UUID,
            primaryKey: true,
            defaultValue: Sequelize.UUIDV1
        },
        cadeauA: {
            type: Sequelize.UUID
        },
        participantId: {
            type: Sequelize.UUID
        },
        tirageUuid: {
            type: Sequelize.UUID
        },
        isAdmin: {
            type: Sequelize.BOOLEAN
        }
    });
    return Participe;
  };

I add records thanks to this piece of code :

const user = await User.findOne({
    where: {
      uuid: req.body.user
    }
  });

  const tirage = await Tirage.create(tirageOptions);

  if (tirage && user) {
    await user.addParticipatingTiragesParticipant(tirage, { through: { isAdmin: true } });
  }

When I add one tirage to an user I just created, it works, but if I try to add another tirage on the same user, it doesn't, the error is :

errors: [
    ValidationErrorItem {
      message: 'undefined must be unique',
      type: 'unique violation',
      path: 'undefined',
      value: null,
      origin: 'DB',
      instance: null,
      validatorKey: 'not_unique',
      validatorName: null,
      validatorArgs: []
    }
  ],

I saw on other forums that the problem is about m:n association in sequelize but I didn't find anything to fix it on my side. And I also saw in my database that I got some constraints created :

constraints in database image

And this is one of them creating the error :

Error in console image

Do you know what to do about it ?

Don't hesitate if you need further details about my project/techs I use. Thanks a lot.

Here is the SQL generated for creating the table participes :

Executing (default): DROP TABLE IF EXISTS `participes`;
Executing (default): CREATE TABLE IF NOT EXISTS `participes` (`uuid` CHAR(36) BINARY , `cadeauA` CHAR(36) BINARY, `participantId` CHAR(36) 
BINARY, `tirageUuid` CHAR(36) BINARY, `isAdmin` TINYINT(1), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, UNIQUE `participes_tirageUuid_cadeauA_unique` (`cadeauA`, `tirageUuid`), UNIQUE `participes_participantId_tirageUuid_unique` (`participantId`), PRIMARY KEY 
(`uuid`)) ENGINE=InnoDB;

Answer 1

I fixed it, I had to declare my associations like that :

db.users.belongsToMany(db.tirages, { through: { model: db.participe, unique: false }, foreignKey: 'participantId', constraints: false, as: 'participatingTiragesParticipant' });
db.users.belongsToMany(db.tirages, { through: { model: db.participe, unique: false }, foreignKey: 'cadeauA', constraints: false, as: 'CadeauTirage' });
db.tirages.belongsToMany(db.users, { through: { model: db.participe, unique: false }, foreignKey: 'tirageUuid', constraints: false, as: 'participatingTiragesTirage' });

Thanks a lot for your help.

Answer 2

use constraints: false in association

db.users.belongsToMany(db.tirages, {through: db.participe, foreignKey:'participantId', constraints: false, as: 'participatingTiragesParticipant'});

db.users.belongsToMany(db.tirages, {through: db.participe, foreignKey:'cadeauA', constraints: false, as: 'CadeauTirage'});

db.tirages.belongsToMany(db.users, {through: db.participe, foreignKey:'tirageUuid', constraints: false, as: 'participatingTiragesTirage'})

Answer 3

Maybe nothing to do with it but I had the same problem and your post comes up first on Google. So if you have the same problem and your MySQL is already configured in english skip this response.

If your MySQL is not configured in english, the regex is no longer functional.. I don't know why they don't use MySQL error codes

When you read the source code of Sequelize, in MySQL part you can see the regex that creates the problem : sequelize/packages/core/src/dialects/mysql /query.js

The easiest solution is just to configure your MySQL in the language of shakespear:

• Find your MySQL config file "my.ini"
• Search "lc-messages" property and replace the value by "en_US"