How to define unique index on multiple columns in sequelize
Mysqlsequelize.jsMysql Problem Overview
How do I define a unique index on a combination of columns in sequelize. For example I want to add a unique index on 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,
})
Mysql Solutions
Solution 1 - Mysql
You can refer to this doc http://docs.sequelizejs.com/en/latest/docs/models-definition/#indexes
You will need to change your definition like shown below and call sync
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']
}
]
});
Solution 2 - Mysql
> I have same issue to applied composite unique constraint to multiple > columns but nothing work with Mysql, Sequelize(4.10.2) and NodeJs > 8.9.4 finally I fixed through 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']
}
}
});
Solution 3 - Mysql
If the accepted one is not working then try the below code. It worked for me in my case rather the accepted one.
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',
}
});
Solution 4 - Mysql
I tried to create an index on a single column. This worked for me. Hope this helps.
Model
module.exports = (sequelize, DataTypes) => {
const Tag = sequelize.define(
"Tag",
{
name: { type: DataTypes.STRING, unique: true },
nVideos: DataTypes.INTEGER
},
{
indexes: [
{
unique: true,
fields: ["name"]
}
]
}
);
return Tag;
};
Migration
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable(
"Tags",
{
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
name: {
type: Sequelize.STRING,
unique: "unique_tag"
},
nVideos: { type: Sequelize.INTEGER },
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
},
{
uniqueKeys: {
unique_tag: {
customIndex: true,
fields: ["name"]
}
}
}
);
},
down: queryInterface => {
return queryInterface.dropTable("Tags");
}
};
Solution 5 - Mysql
I prefer sequelize sync method with composite unique, If not passing indexes name u will get a error as below on adding many indexes in index array.
> error: SequelizeDatabaseError: Identifier name 'LONG_NAME' is too long
module.exports = function (sequelize: any, DataTypes: any) {
return sequelize.define('muln_user_goals_transaction', {
id: {
type: DataTypes.INTEGER(11),
allowNull: false,
autoIncrement: true,
primaryKey: true,
},
name: {
type: DataTypes.STRING(),
allowNull: false,
},
email: {
type: DataTypes.STRING(),
allowNull: false,
},
phone: {
type: DataTypes.STRING(),
allowNull: false,
},
amount: {
type: DataTypes.INTEGER(8),
allowNull: false
},
deleted: {
type: DataTypes.BOOLEAN,
defaultValue: false,
},
}, {
tableName: 'muln_user_goals_transaction',
timestamps: false,
indexes: [
{
name: 'unique_index',
unique: true,
fields: ['name', 'email', 'phone', 'amount', 'deleted']
}
],
defaultScope: {
where: {
deleted: false
}
}
});
};
Solution 6 - Mysql
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');
}
};