How to define a unique index for multiple columns in sequelize

Question

How to define a unique index in a combination of columns in sequelize. For example, I want to add a unique index to user_id, count and name.

var Tag = sequelize.define('Tag', { id: { type: DataTypes.INTEGER(11), allowNull: false, primaryKey: true, autoIncrement: true }, user_id: { type: DataTypes.INTEGER(11), allowNull: false, }, count: { type: DataTypes.INTEGER(11), allowNull: true }, name: { type: DataTypes.STRING, allowNull: true, }) 

Answer 1

You can refer to this document http://docs.sequelizejs.com/en/latest/docs/models-definition/#indexes

You need to change the definition as shown below and trigger the synchronization

 var Tag = sequelize.define('Tag', { id: { type: DataTypes.INTEGER(11), allowNull: false, primaryKey: true, autoIncrement: true }, user_id: { type: DataTypes.INTEGER(11), allowNull: false, }, count: { type: DataTypes.INTEGER(11), allowNull: true }, name: { type: DataTypes.STRING, allowNull: true, } }, { indexes: [ { unique: true, fields: ['user_id', 'count', 'name'] } ] }); 

Answer 2

I have the same problem with applying a compound unique constraint to multiple columns, but nothing works with Mysql, Sequelize (4.10.2) and NodeJs 8.9.4 Finally, I fixed the following code.

 queryInterface.createTable('actions', { id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true }, system_id: { type: Sequelize.STRING, unique: 'actions_unique', }, rule_id: { type: Sequelize.STRING, unique: 'actions_unique', }, plan_id: { type: Sequelize.INTEGER, unique: 'actions_unique', } }, { uniqueKeys: { actions_unique: { fields: ['system_id', 'rule_id', 'plan_id'] } } }); 

Answer 3

If the accepted does not work, try the following code. In my case, it worked, not accepted.

 var Tag = sequelize.define('Tag', { id: { type: DataTypes.INTEGER(11), allowNull: false, primaryKey: true, autoIncrement: true }, user_id: { type: DataTypes.INTEGER(11), allowNull: false, unique: 'uniqueTag', }, count: { type: DataTypes.INTEGER(11), allowNull: true, unique: 'uniqueTag', }, name: { type: DataTypes.STRING, allowNull: true, unique: 'uniqueTag', } }); 

Answer 4

Sequelize composite unique (manual)

 module.exports = { up: (queryInterface, Sequelize) => { return queryInterface.createTable('Model', { id: { allowNull: false, autoIncrement: true, primaryKey: true, type: Sequelize.INTEGER }, fieldOne: { type: Sequelize.INTEGER, unique: 'uniqueTag', allowNull: false, references: { model: 'Model1', key: 'id' }, onUpdate: 'cascade', onDelete: 'cascade' }, fieldsTwo: { type: Sequelize.INTEGER, unique: 'uniqueTag', allowNull: false, references: { model: 'Model2', key: 'id' }, onUpdate: 'cascade', onDelete: 'cascade' }, createdAt: { allowNull: false, type: Sequelize.DATE }, updatedAt: { allowNull: false, type: Sequelize.DATE } }) .then(function() { return queryInterface.sequelize.query( 'ALTER TABLE 'UserFriends' ADD UNIQUE 'unique_index'('fieldOne', 'fieldTwo')' ); }); }, down: (queryInterface, Sequelize) => { return queryInterface.dropTable('Model'); } };