sequelize: Keep getting unique validation error: How to allow duplicate entries in N:M (belongsToMany)?

I met the same thing: unique: false does not prevents creation of unique index. First I came to google and found this post. And I’ve tried as @mickhansen said:

Ah well that’s great, that’s what you have to do. You might need to tweak the belongsToMany call to accept uniques: Item.belongsToMany(models.user, { through: models.history, as: “Owned”, unique: false});

But this is not correct. After deep debugging I’ve found that belongsToMany expects the complex object ‘through’ if we want to set additional options to the relation. So the correct code is:

Item.belongsToMany(models.user, { through: { model: models.history, as: "Owned", unique: false}});

As I’ve found this, I search the sequel docs for word ‘unique’. And found this feature in n:m chapter: http://docs.sequelizejs.com/en/latest/docs/associations/?highlight=unique: false#nm

See the example for Post:

    Post.belongsToMany(Tag, {
        through: {
            model: ItemTag,
            unique: false,
            scope: {
                taggable: 'post'
            }
        },
        foreignKey: 'taggable_id',
        constraints: false
});

P.S. Maybe this options may be covered in manual more clearly. I’ve read man several times, but ‘unique: false’ feature was discovered only with Google.

This should be mentioned in docs very explicitly.

For anyone with this issue, here is a suggested workaround:

// Instead of using a Many-to-Many relationship:
A.belongsToMany(B, { through: C });
B.belongsToMany(C, { through: C });

// Throw away the Many-to-Many relationship altogether and treat C as a full-fledged standard model
A.hasMany(C);
C.belongsTo(A);
B.hasMany(C);
C.belongsTo(B);

However the solution above of course will change how you fundamentally perform includes and the like.

// This way, instead of
A.findAll({
  include: B
});

// You will have to do
A.findAll({
  include: {
    model: C
    include: B
  }
})
// and the structure of the query result will be a bit different but all the content you need will be there

This code will help you. Look at this: I have found that primaryKey must be false and I did it with adding to my associations primaryKey: false

// Tag model
  let Tag = sequelize.define(
    "Tag",
    {
      type: {type: DataTypes.ENUM, values: ['A', 'B'], unique: 'item_tag_taggable', defaultValue: 'A', allowNull: false},
      userId: {type: DataTypes.INTEGER, unique: 'item_tag_taggable'},
      relId: {type: DataTypes.INTEGER, unique: 'item_tag_taggable', references: null},
    },
    {
        charset: "utf8",
        collate: "utf8_general_ci",
        underscored: true,
        timestamps: false,
    }
  );

// Associations

ItemA.belongsToMany(models.User, { through: { model:"Tag", unique: false, scope: { type: 'A' }}, as: "ItemATags", foreignKey: { name: "relId", primaryKey: false, references: null }, constraints: false});
ItemB.belongsToMany(models.User, { through: { model:"Tag", unique: false, scope: { type: 'B' }}, as: "ItemBTags", foreignKey: { name: "relId", primaryKey: false, references: null }, constraints: false});

User.belongsToMany(models.ItemA, { through: { model: "Tag", unique: false }, as: "ItemATags", foreignKey: { name: 'userId', primaryKey: false}});
User.belongsToMany(models.ItemB, { through: { model: "Tag", unique: false }, as: "ItemBTags", foreignKey: { name: 'userId', primaryKey: false}});

Main thing there primaryKey must be false And I didn’t optimize my code, you can do it 😃

The unique constraint error deletes the previous records too. This isn’t expected. Please resolve the issue

Hi, is this still ongoing?

I need the relation table to avoid having a composite primary key with the 2 relation fields, is that possible?

Perhaps we can give it the field name for the primary key?

Shall I need to create my own model for this and then set unique: false on the BelongsToMany ?

Forgive me if this a noob thing to say, I’m new to Sequelize and am a little rusty on SQL, but it looks like when you create belongsToMany tables, for example:

| productId | userId |       createdAt        |
|-----------|--------|------------------------|
|       123 |    456 | 2015-05-10 23:00:00+00 |

It’s using the productId, userId together as the primary key. While this could work fine for pure unique relationships, it doesn’t handle the circumstances where the same relation can be created multiple times - because the primary key has to be unique.

For example, purchasing/viewing the same product multiple times.

To get around this I’ve created a separate id primary key on the relational table, using an auto increment.

Don’t know if this is the best solution but it certainly works.

unique entries in N:M is currently not supported. You can use unique: false for the options on the N:M relation but it only gets you halfway there (the setters will not work ideally so you manually have to create entries).