Sequelize composite unique constraint

Question

Defining model:

export default function(sequelize, DataTypes) {
   return sequelize.define('Item', {
       minor: DataTypes.INTEGER,
       major: DataTypes.INTEGER,
   });
}

Can I define the pair minor and major as a composite UNIQUE constraint?

Answer 1

Recently, in V4, Sequelize has a method addConstraint on the query interface:

queryInterface.addConstraint('Items', {   fields: ['minor', 'major'],   type: 'unique',   name: 'custom_unique_constraint_name' }); 

Answer 2

queryInterface.createTable('Item', {     minor: {         type: Sequelize.INTEGER,     },     major: {         type: Sequelize.INTEGER,     } }, {     uniqueKeys: {         Items_unique: {             fields: ['minor', 'major']         }     } }); 

Answer 3

Here is the simple answer:

major: { type: DataTypes.INTEGER, unique: 'compositeIndex'},
minor: { type: DataTypes.INTEGER, unique: 'compositeIndex'}

source: https://sequelize.org/master/manual/model-basics.html#column-options

// Creating two objects with the same value will throw an error. > The unique property can be either a
// boolean, or a string. If you provide the same string for > multiple columns, they will form a
// composite unique key.
uniqueOne: { type: DataTypes.STRING,  unique: 'compositeIndex' },
uniqueTwo: { type: DataTypes.INTEGER, unique: 'compositeIndex' },

You can also create a unique constraint through belongsToMany associations if it's a join table:

Major = sequelize.define('major', {})
Minor = sequelize.define('minor', {})

Major.belongsToMany(Project)
Minor.belongsToMany(User)

Source: http://docs.sequelizejs.com/en/v3/docs/associations/

In my case, I wanted to find a way to enforce this with my migration. I did this by appending a raw sql query at the end of my up function:

  up: function(queryInterface, Sequelize) {
return queryInterface.createTable('Item', {
  major: {
    allowNull: false,
    type: Sequelize.INTEGER
  },
  minor: {
    allowNull: false,
    type: Sequelize.INTEGER
  },
})
.then(function() {
  return queryInterface.sequelize.query(
    'ALTER TABLE `Item` ADD UNIQUE `unique_index`(`major`, `minor`)'
  );
});

Related Questions:

Sequelize, foreign keys as composite primary key

Unique constraint across foreign keys in Sequelize model

Answer 4

I solved this by using addConstraint(...):

await queryInterface.addConstraint('Item', {
  fields: ['minor', 'major'],
  type: 'unique',
  name: 'unique_constraint_name'
});

This is as of at least v6+.