node.js - 使用关联数据创建复合索引？

Question

我有一个用户表和一个用户属性表。每个用户只能拥有每个 userAttribute 的一个实例，所以我想为列名称和 userId 创建一个复合唯一索引(由 userAttributes.belongsTo 创建。)如何做到这一点？

UserAttribute = sequelize.define('userAttributes', {
    name: {
        type:       Sequelize.STRING,
        allowNull:  false,
        unique: 'nameIndex',
        validate: {
            isIn: [['phone', 'name','driverRequest']],
        }
    },
    value: {
        type:       Sequelize.STRING,
        allowNull:  false
    },
});


User.hasMany(userAttributes, {unique: 'nameIndex'});
userAttributes.belongsTo(User, {unique: 'nameIndex'});

我试图添加唯一的 nameIndex 但没有成功，它似乎只适用于名称列。

Answer 1

var Sequelize = require('sequelize');
var sequelize = new Sequelize('<yourDatabaseName>', '<yourUserName>', '<yourPassword', {
  host: '<ip>'
});

var User = sequelize.define('user', {
  id: {
    type: Sequelize.STRING,
    allowNull: false,
    primaryKey: true
  },
});
var UserAttribute = sequelize.define('userattribute', {
  userId: {
    type: Sequelize.STRING,
    allowNull: false,
    unique: 'compositeIndex',
    references: {
      model: User,
      key: "id"
    }
  },
  name: {
    type: Sequelize.STRING,
    allowNull: false,
    unique: 'compositeIndex'
  }
});

User.hasOne(UserAttribute, {
  as: "UserAttribute"
})

UserAttribute.belongsTo(User, {
  foreignKey: "userId",
  as: 'UserId'
})


sequelize.sync({
  // use force to delete tables before generating them
  force: true
}).then(function() {
  console.log('tables have been created');
  return User.create({
    id: 'randomId1'
  });
})
  .then(function() {
    console.log('tables have been created');
    return User.create({
      id: 'randomId2'
    });
  })
  .then(function() {
    return UserAttribute.create({
      userId: 'randomId1',
      name: 'name1'
    });
  })
  .then(function() {
    return UserAttribute.create({
      userId: 'randomId2',
      name: 'name1'
    });
  })
  // // generates Validation Error
  // .then(function() {
  //   return UserAttribute.create({
  //     userId: 'randomId1',
  //     name: 'name1'
  //   });
  // })