Sequelize，外键作为复合主键

it is possible to define two foreign keys as a composite primary key of a model?

可以将两个外键定义为模型的复合主键吗?

A user can only be a member of one family, a family can have many members and the family-members table need the references of the user and family

用户只能是一个家庭的成员,一个家庭可以有许多成员,家庭成员表需要用户和家庭的参考

const User = sequelize.define(
    'User',
    {
        id: { type: dataTypes.INTEGER.UNSIGNED, autoIncrement: true, primaryKey: true },
        name: { type: dataTypes.STRING(30) },
        email: { type: dataTypes.STRING(30) }
        ...
    },
    {
        classMethods: {
            associate(models) {
                User.hasOne(models.FamilyMember, {
                    foreignKey: 'user_id'
                }
            }
        }
    }
)

const Family = sequelize.define(
    'Family',
    {
        name: { type: dataTypes.STRING(30) }
    },
    {
        classMethods: {
            associate(models) {
                Family.hasMany(models.FamilyMember, {
                    foreignKey: 'family_id'
                }
            }
        }
    }
)

const FamilyMember = sequelize.define(
    'FamilyMember',
    {
        name: { type: dataTypes.STRING(30) },
        /*
        family_id and user_id will be here after associations but I wanted them to be a composite primaryKey
        */
    }
)

2 个解决方案

#1

In fact, almost I got the solution from documentation:

事实上,我几乎从文档中得到了解决方案:

User = sequelize.define('user', {});
Project = sequelize.define('project', {});
UserProjects = sequelize.define('userProjects', {
    status: DataTypes.STRING
});

User.belongsToMany(Project, { through: UserProjects });
Project.belongsToMany(User, { through: UserProjects });

By default the code above will add projectId and userId to the UserProjects table, and remove any previously defined primary key attribute - the table will be uniquely identified by the combination of the keys of the two tables, and there is no reason to have other PK columns.

默认情况下,上面的代码会将projectId和userId添加到UserProjects表中,并删除任何先前定义的主键属性 - 该表将由两个表的键组合唯一标识,并且没有理由拥有其他PK列。

Source

#2

For anyone looking to create a composite index primary key based of the columns(keys) in your join table when doing migrations. You will need to add a primary key constraint for the two columns that you wish to act as the combined primary key for the table.

对于希望在进行迁移时根据连接表中的列(键)创建复合索引主键的任何人。您需要为希望充当表的组合主键的两列添加主键约束。

module.exports = {
  up: function (queryInterface, Sequelize) {
    return queryInterface.createTable('itemtags', {
      itemId: {
        type: Sequelize.INTEGER,
        references: {
          model: 'items',
          key: 'id',
        },
        onDelete: 'CASCADE',
        onUpdate: 'CASCADE',
        allowNull: false
      },
      tagId: {
        type: Sequelize.INTEGER,
        references: {
          model: 'tags',
          key: 'id',
        },
        onDelete: 'CASCADE',
        onUpdate: 'CASCADE',
        allowNull: false
      }
    })
      .then(() => {
        return queryInterface.addConstraint('itemtags', ['itemId', 'tagId'], {
          type: 'primary key',
          name: 'gametag_pkey'
        });
      });
  },
  down: function (queryInterface, Sequelize) {
    return queryInterface.dropTable('gametags');
  }
};

Which is roughly the same as doing ALTER TABLE ONLY my_table ADD CONSTRAINT pk_my_table PRIMARY KEY(column1,column2); in postgres.

这与ALTER TABLE ONLY大致相同my_table ADD CONSTRAINT pk_my_table PRIMARY KEY(column1,column2);在postgres。

#1