Composite primary key in junction table - Sequelize

Question

Working with Sequelize and MySQL database, I was trying to achieve composite primary keys combination in junction table, but unfortunately without result.

I have tables:

They are in relation many to many. In junction table user_has_project I want two primary keys combination: user_id and project_id.

Sequelize models definition:

User:

module.exports = function(sequelize, Sequelize) {

    var User = sequelize.define('user', {
        id: {
            autoIncrement: true,
            primaryKey: true,
            type: Sequelize.INTEGER(11)
        },

        name: {
            type: Sequelize.STRING(100),
            allowNull: false
        }
    },{
        timestamps: false,
        freezeTableName: true,
        underscored: true
});

User.associate = function (models) {
    models.user.belongsToMany(models.project, {
        through: 'user_has_project',
        foreignKey: 'user_id',
        primaryKey: true
    });
};

    return User;
}

Project:

module.exports = function(sequelize, Sequelize) {

    var Project = sequelize.define('project', {
        id: {
            autoIncrement: true,
            primaryKey: true,
            type: Sequelize.INTEGER(11)
        },

        name: {
            type: Sequelize.STRING(100),
            allowNull: false
        }
    },{
        timestamps: false,
        freezeTableName: true,
        underscored: true
    });


Project.associate = function (models) {
    models.project.belongsToMany(models.user, {
        through: 'user_has_project',
        foreignKey: 'project_id',
        primaryKey: true
    });
};

    return Project;
}

I was trying to 'force' Primary keys definition in user_has_project table using "primaryKey: true" in both models association, but definition above is creating only user_id as PRI and project_id as MUL

score 1 · Accepted Answer · answered Mar 18 '18 at 04:54

1

What is Sequelize version? I tested Sequelize 4 in sqlite3, the definition above makes a query

CREATE TABLE IF NOT EXISTS `user_has_project` (`created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, `user_id` INTEGER(11) NOT NULL REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, `project_id` INTEGER(11) NOT NULL REFERENCES `project` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (`user_id`, `project_id`));

Is "PRIMARY KEY (user_id, project_id)" what you want?

answered Mar 18 '18 at 04:54

eseom

86
2

I am using Sequelize 4.33.4. I've tested it and It was working before as well, problem was with application that I am using 'Sequel Pro' in summary of table is showing as MUL but in reality they are both PRI. I went to phpMyAdmin to check and there was correct. Anyway thank you for answer. – Marcin Mar 18 '18 at 11:16

Composite primary key in junction table - Sequelize

1 Answers1