使用关联数据创建复合索引? [英] Create a composite index with associated data?

问题描述

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

I have a User-table and a userAttributes table. Every user can only have one instance of each userAttribute, so I would like to create a composite unique index for the columns name and userId(That is created by userAttributes.belongsTo.) How can this be done?

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但没有成功，它似乎只适用于name-column.

I tride adding the unique nameIndex with no success, it seems to only apply to the name-column.

推荐答案

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

这篇关于使用关联数据创建复合索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！