CODEWITHSUNDEEP
postgresqlsequelize.js
Geraint Ballinger
Geraint Ballinger

Reputation: 335

Sequelize Duplicate Key Constraint Violation

I am trying to add a many to many relationship through an explicitly created junction table using Sequelize and Postgresql.

The tables on either side of the relationship are associated like this:

Shop.belongsToMany(models.user, {through: 'visits' })
User.belongsToMany(models.shop, {through: 'visits' })

And the visits junction table primary key is defined like this:

id: {
 type: DataTypes.INTEGER,
 primaryKey: true,
 autoIncrement: true // Automatically gets converted to SERIAL for postgres
}

When I try and insert into visits I get the following error:

ERROR:  duplicate key value violates unique constraint "visits_shopId_userId_key"
DETAIL:  Key ("shopId", "userId")=(1, 12) already exists.

After doing a pg_dump, I have tried to remove the composite key constraint by adding constraint: false to the models, but I still get the error.

(I have dropped the tables and resynced several times during the debugging process)

Upvotes: 3

Views: 5236

Answers (1)

Laurence
Laurence

Reputation: 671

After digging around the Sequelize issues, it turns out that removing the constraint on the N:M composite key is an easy fix.

The through key can take an object with the unique: false property:

Shop.belongsToMany(models.user, {
    through: {
        model: 'visits',
        unique: false
    },
    constraints: false
});

Upvotes: 3

Related Questions