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

[maplesap]

The unique validation check is preventing me from creating duplicate entries in my join table:

// models/user.js
User.belongsToMany(models.item, { through: models.history, as: "Owners', 
  foreignKey: { 
    name: "itemId",
    allowNull: true,
    unique: false
  }
});

// models/item.js
Item.belongsToMany(models.user, { through: models.history, as: "Owned", 
  foreignKey: {
    unique: false
  }
});

// models/history.js
id: {
  type: DataTypes.INTEGER,
  primaryKey: true,
  autoIncrement: true
},
other: DataTypes.STRING

When a user buys the same item again, resulting in duplicate combinations of itemId and userId in history table, sequelize will throw validation errors:

{ name: 'SequelizeUniqueConstraintError',
  message: 'Validation error',
....
message: "userId" must be unique
type: 'unique violation'
....
message: "itemId" must be unique
type: 'unique violation'

But I have set unique: false on the foreignkeys, and have created a primaryKey for history table, so this validation error confused me. What's the proper way to allow duplicate entries in N:M?

[mickhansen]

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).

[maplesap]

Can you explain further with what you mean "so you manually have to create entries". Right now to create the history, I just do:

models.History.create({userId: user_id, itemId: item_id});

Which triggers the unique validation errors if a duplicate of userId and itemId already exists.

Do you mean I should remove my belongsToMany relations on user and item models, and I should just manually create the join table like:

// models/history.js
id: {
  type: DataTypes.INTEGER,
  primaryKey: true,
  autoIncrement: true
},
userId: DataTypes.INTEGER,
itemId: DataTypes.INTEGER,
other: DataTypes.STRING

[mickhansen]

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});

(the unique: false prop is on the relation, not on the foreign key option)

[maplesap]

Hm, I'm still getting the unique validation errors for both itemId and userId:

User.belongsToMany(models.item, { through: models.history, as: "Owners', unique: false,
  foreignKey: { 
    allowNull: true  }
});

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

[mickhansen]

@maplesap what does the create table sql look like?

[maplesap]

This is the output:

Executing (default): INSERT INTO "History" ("id","other","userId","itemId","updatedAt","createdAt") VALUES (DEFAULT,"blah",1,2,'2015-02-25 17:12:20.221 +00:00','2015-02-25 17:12:20.221 +00:00') RETURNING *;
{ name: 'SequelizeUniqueConstraintError',
  message: 'Validation error',
  errors: 
   [ { message: '"itemId" must be unique',
       type: 'unique violation',
       path: '"itemId"',
       value: '2' },
     { message: '"userId" must be unique',
       type: 'unique violation',
       path: '"userId"',
       value: '1' } ],
  fields: { '"itemId"': '2', '"userId"': '1' } }

[mickhansen]

@maplesap that's not the create table statement though :)
I assume you are using sync({force: true}), otherwise the constraints in your database won't change.

[maplesap]

Okay! I created a small version with the problem. Here is the entire code:

app.js

var express = require('express');
var app = express();
var http = require('http').Server(app);

var bodyParser = require('body-parser');
app.use(bodyParser.json());
app.use(express.static(__dirname + '/public'));

app.post("/test", function(req, res) {

    models.history.create({other: "blah", itemId: 1, userId: 1}).catch(function(error) {
        console.log(error);
    });

});

var models = require('./models')
models.sequelize.sync({logging: console.log}).then(function() {
    http.listen(3000, function() {});
});

models/user.js

"use strict";

module.exports = function(sequelize, DataTypes) {
  var User = sequelize.define("user", {
    name: DataTypes.STRING
  }, {
    timestamps: false,
    classMethods: {
        associate: function(models) {
            User.belongsToMany(models.item, { through: models.history, as: 'Owners', unique: false, foreignKey: { name: 'userId', allowNull: true }});
        }
    }

  });

  return User;
};

models/item.js

"use strict";

module.exports = function(sequelize, DataTypes) {
    var Item = sequelize.define('item', {
        name: DataTypes.STRING
    }, {
        timestamps: false,
        classMethods: {
            associate: function(models) {
                Item.belongsToMany(models.user, { through: models.history, as: 'Owned', unique: false });
            }
        }
    });

    return Item;
};

models/history.js

"use strict";

module.exports = function(sequelize, DataTypes) {
    var History = sequelize.define("history", {
        id: {
            type: DataTypes.INTEGER,
            primaryKey: true,
            autoIncrement: true,
        },
        other: DataTypes.STRING
    });

    return History;
};

index.html

<html>
    <body>
        <script src="//ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
    </body>
</html>

<script>
$.post("/test");
</script>

Here is the output when I first run the app:

Executing (default): CREATE TABLE IF NOT EXISTS "items" ("id"   SERIAL , "name" VARCHAR(255), PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND a.attnum = ANY(ix.indkey) AND t.relkind = 'r' and t.relname = 'items' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): CREATE TABLE IF NOT EXISTS "users" ("id"   SERIAL , "name" VARCHAR(255), PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND a.attnum = ANY(ix.indkey) AND t.relkind = 'r' and t.relname = 'users' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): CREATE TABLE IF NOT EXISTS "histories" ("id"  SERIAL , "other" VARCHAR(255), "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, "itemId" INTEGER REFERENCES "items" ("id") ON DELETE CASCADE ON UPDATE CASCADE, "userId" INTEGER REFERENCES "users" ("id") ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE ("itemId", "userId"), PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND a.attnum = ANY(ix.indkey) AND t.relkind = 'r' and t.relname = 'histories' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;

When I insert a duplicate combination of userId and itemId, the output is:

Executing (default): INSERT INTO "histories" ("id","other","itemId","userId","updatedAt","createdAt") VALUES (DEFAULT,'blah',1,1,'2015-02-25 21:28:09.957 +00:00','2015-02-25 21:28:09.957 +00:00') RETURNING *;
{ name: 'SequelizeUniqueConstraintError',
  message: 'Validation error',
  errors: 
   [ { message: '"itemId" must be unique',
       type: 'unique violation',
       path: '"itemId"',
       value: '1' },
     { message: '"userId" must be unique',
       type: 'unique violation',
       path: '"userId"',
       value: '1' } ],
  fields: { '"itemId"': '1', '"userId"': '1' } }

[maplesap]

For the moment I kind of cheated and created belongsTo for history:

history
belongsTo(models.user)
belongsTo(models.item)

Which won't cause the unique validation error to occur and I can set my ids.

It seems like setting unique: false is getting ignored for belongsToMany? This line:
https://github.com/sequelize/sequelize/blob/master/lib/dialects/postgres/query-generator.js#L69

Is returning true and uniqueKeys always gives fields: [ 'itemId', 'userId' ]. So somewhere before this must be skipping the unique check.

[andycbriggs]

I'm seeing this same issue and have created this short gist where I was able to replicate the problem using some examples from the docs.

https://gist.github.com/kaidoe/a8a46fc56e09f67ea956

[chrisjhoughton]

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.

[mickhansen]

@chrisjhoughton I'm afraid we have very poor support for non-unique N:M relations at the time.

[chrisjhoughton]

@mickhansen no problem, the workaround above is working great!

[j7caiman]

@chrisjhoughton
Hi - I'm having the same problem as you I think, but even when I try your workaround, it doesn't seem to work. Is this what you were doing?

var Doctor = sequelize.define('doctor', {
    name: Sequelize.STRING
});

var Patient = sequelize.define('patient', {
    name: Sequelize.STRING
});

var Appointment = sequelize.define('appointment', {
    id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    date: Sequelize.DATE,
    paymentType: Sequelize.STRING
});

Doctor.belongsToMany(Patient, {through: Appointment, unique: false, foreignKey: {name: "patientId", unique: false}});
Patient.belongsToMany(Doctor, {through: Appointment, unique: false, foreignKey: {name: "doctorId", unique: false}});

Even still, it creates:

    "appointments_doctorId_patientId_key" UNIQUE CONSTRAINT, btree ("doctorId", "patientId")

I realize some of those unique: true may be redundant / nonsensical but I cannot figure out how to make it work.

[zachguo]

@j7caiman In your last two lines, try following instead

Appointment.belongsTo(Patient, ...
Appointment.belongsTo(Doctor, ...