How to define unique index on multiple columns in sequelize

Solution 1:

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:

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:

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:

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