Sequelize and Postgres - foreign key constraint "..." cannot be implemented

Question

I'm trying to add two foreign keys into my transaction table, one of then works just fine, but the second can't be implemented. Is there a way to set an array of foreign keys? I suppose the problem is the array, since it's the only thing different.

'use strict';

module.exports = {
  up: (queryInterface, Sequelize) => {
      return queryInterface.createTable('transactions', { 
        id: 
        {
             type: Sequelize.INTEGER,
             primaryKey: true,
             autoIncrement: true,
             allowNull: false,
        },
        value:
        {
          type: Sequelize.FLOAT,
          allowNull: false,
        },
        clientId:
        {
          type: Sequelize.INTEGER,
          allowNull: true,
          references: { model: 'client', key: 'id'},
          onUpdate: 'CASCADE',
          onDelete: 'CASCADE'
        },
        productId:
        {
          type: Sequelize.ARRAY(Sequelize.INTEGER),
          allowNull: false,
          references: { model: 'products', key: 'id'},
          onUpdate: 'CASCADE',
          onDelete: 'CASCADE'
        },
        createdAt:
        {
          type: Sequelize.DATE,
          allowNull: false,
        },
        updatedAt: 
        {
          type: Sequelize.DATE,
          allowNull: false,
        }
      });
  },

  down: (queryInterface, Sequelize) => {
      return queryInterface.dropTable('transactions');
  }
};

Code Fraction:

I don't think you need a foreign key of type `Array`, if your tables relationship constructed properly, can you share the other models schema too? it would be helpful to give you a proper answer. — ROOT, Jan 07 '20 at 12:29

score 0 · Accepted Answer · edited Jan 07 '20 at 14:01

Yeah, your foreign key is probably actually a VARCHAR or a BIGINT or something along those lines... It's probably choking to death on that... your model will end up with a hasMany relationship or something that handles the fact that you may have many products associated with a transaction through a join table of some sort.... so your tables might be something like transaction, product, transaction_product with established relationships

transaction.hasMany(product, {as: "Products", through: "transaction_product", foreignKey: "transaction_id"}

and your product model might have something like

product.belongsToMany(transaction, {as: "Transactions", through: "transaction_product", foreignKey: "product_id"}

and this would take care of the many to many you are likely trying to compensate for

Sequelize and Postgres - foreign key constraint "..." cannot be implemented

1 Answers1