Composite Primary Key in Sequelize

model-user.js

/*
 * Migration
 */
'use strict';
module.exports = {
  up: function(queryInterface, Sequelize) {
    return queryInterface.createTable('Users', {
      firstName: {
        type: Sequelize.STRING
      },
      lastName: {
        type: Sequelize.STRING
      },
      email: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    })
    .then(() => {
      return queryInterface.sequelize.query('ALTER TABLE "Users" ADD CONSTRAINT "username" PRIMARY KEY ("firstName", "lastName")');
    })
  },
  down: function(queryInterface, Sequelize) {
    return queryInterface.dropTable('Users');
  }
};

/*
 * Model
 */
'use strict';
module.exports = function(sequelize, DataTypes) {
  var User = sequelize.define('users', {
    firstName: {
      type: DataTypes.STRING,
      primaryKey: true,
    },
    lastName: {
      type: DataTypes.STRING,
      primaryKey: true,
    },
    email: DataTypes.STRING
  });
  User.removeAttribute('id');
  return User;
};

This gist save me the day !!! I just modified the create compound primary sentence to use queryInterface.addConstraint instead a raw query.
Thanks !!!

Hey just came across this gist and it really helped me. After some digging, I just wanted to point out that you could also add the constraint like this if you want to avoid the raw sql:

queryInterface.addConstraint('Users', ['firstName', 'lastName'], {
      type: 'primary key',
      name: 'users_pkey'
    });

You can also just say primaryKey:true on several columns, and sequelize will understand that it's a composite key. At least, it does on top of postgres.

(Against sequelize@4.33.4)

How do we use findOne() method in this case? I want to find a record given the first name and the last name using findOne() method which looks up the row using the primary key

@narayana1043
You've still got to use the where clause in findOne()
Something like this findOne({where:{composite_key_1:value})

Let me know if this was helpful, Thanks.

I did like @jmlsf mentioned, but had to add 'SET FOREIGN_KEY_CHECKS = 0'

ERROR: Cannot change column 'itemId': used in a foreign key constraint 'table_itemId_foreign_idx'

.then(() => queryInterface.sequelize.query('SET FOREIGN_KEY_CHECKS = 0'))
      .then(() =>
        queryInterface.addConstraint(
          'table',
          ['itemId', 'item2Id'],
          {
            type: 'primary key',
            name: 'name_ibfk_1'
          }
        )
      )
      .then(() => queryInterface.sequelize.query('SET FOREIGN_KEY_CHECKS = 1')),

@narayana1043
You've still got to use the where clause in findOne()
Something like this findOne({where:{composite_key_1:value})

Let me know if this was helpful, Thanks.

When I create a composite key, by specifying primaryKey: true multiple times, am I able to find out the name of the composite key to use in the where clause?

Thanks

@ narayana1043
Todavía tienes que usar la wherecláusula en findOne()
algo como estofindOne({where:{composite_key_1:value})
Hágame saber si esto fue útil, gracias.

Cuando creo una clave compuesta, al especificar primaryKey: truevarias veces, ¿puedo averiguar el nombre de la clave compuesta para usar en la cláusula where?

Gracias

Hi!!! You could know how to get the name thanks