Geek Answers Handbook

Sequelize, foreign keys as a composite primary key

can two foreign keys be defined as the composite primary key of the model?

A user can only be a member of one family, a family can have many members, and a table of family members needs links to 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 */ } )
+7
source share
2 answers

In fact, I got a solution from the 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 above code will add projectId and userId to the UserProjects table and delete all previously defined attributes of the primary key - the table will be uniquely identified by the combination of keys of the two tables, and there are no reasons for other PK columns.

A source

+7
source share

For anyone who wants to create a primary key of a composite index based on the columns (keys) in your join table when performing migrations. You will need to add a primary key constraint for the two columns that you want to use 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'); } };

This is about the same as when ALTER TABLE ONLY my_table ADD CONSTRAINT pk_my_table PRIMARY KEY(column1,column2); in postgres.

0
source share

More articles:


All Articles