MySQL 如何在sequelize中的多列上定义唯一索引

How to define unique index on multiple columns in sequelize

提问by lboyel

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.

如何在 sequelize 中的列组合上定义唯一索引。例如，我想在 user_id、count 和 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,
        })

回答by Keval Gohil

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

你可以参考这个文档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']
        }
    ]
});

回答by Sunny S.M

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.

我在将复合唯一约束应用于多列时遇到了同样的问题，但对 Mysql、Sequelize(4.10.2) 和 NodeJs 8.9.4 没有任何作用，最后我通过以下代码修复。

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

回答by M.A.K. Ripon

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

回答by Sandeep Ranjan

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

回答by Riajul Islam

Sequelize composite unique (manual)

Sequelize 复合独特（手动）

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