mysql - sequelize error: missing index for constraint

Question

20181005120552-create-order-detail.js

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('OrderDetails', {
      orderDetailId: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        allowNull: false,
        autoIncrement: true,
      },
      orderId: {
        type: Sequelize.INTEGER,
        onDelete: 'CASCADE',
        references: {
          model: 'Orders',
          key: 'orderId'
        }
      },
      productName: {
        type: Sequelize.STRING,
        primaryKey: true,
        allowNull: false,
      },
      count: {
        type: Sequelize.INTEGER
      },
      orderDetailPrice: {
        type: Sequelize.INTEGER,
        onDelete: 'CASCADE',
        references: {
          model: 'Orders',
          key: 'totalPrice'
        }
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('OrderDetails');
  }
};

20181005120522-create-order

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface
    .createTable('Orders', {
      orderId: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        allowNull: false
      },
      userId: {
        type: Sequelize.STRING,
        onDelete: 'CASCADE',
        references: {
          model: 'Users',
          key: 'userId'
        }
      },
      orderDate: {
        type: Sequelize.DATE
      },
      totalPrice: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        allowNull: false,
      },
      orderState: {
        type: Sequelize.STRING
      },
      shippingNumber: {
        type: Sequelize.STRING
      },
      basicAddress: {
        type: Sequelize.STRING
      },
      detailAddress: {
        type: Sequelize.STRING
      },
      telNumber: {
        type: Sequelize.INTEGER
      },
      phoneNumber: {
        type: Sequelize.INTEGER
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Orders');
  }
};

When i executed script sequelize db:migrate, previous migration is without errors executed. in this level it returns error. I don't know how can i resolve this problem i guess it has something wrong.

ERROR: Failed to add the foreign key constraint. Missing index for constraint 'orderdetails_ibfk_2' in the referenced table 'orders'

This is error message. I wanna connect files OrderDetails.orderDetailPrice and Orders.totalPrice.

Thanks.

标签： mysqlnode.jsforeign-keysmigrationconstraints

Answer 1

As reported here, it seems that Sequelize has some issues with references to composite keys.

However, by exploiting the Sequelize query execution you can workaround you problem. In you case you can perform the following mysql query:

ALTER TABLE `db_test`.`OrderDetails` ADD CONSTRAINT `fk_order_detailes_orders` 
FOREIGN KEY (`orderId` , `orderDetailId`) 
REFERENCES `db_test `.`orders`(`orderId` , `totalPrice`);

So your create-order-detail migration file becomes the following:

    'use strict';
    module.exports = {
        up: (queryInterface, Sequelize) => {
            return queryInterface.createTable('OrderDetails', {
                orderDetailId: {
                    type: Sequelize.INTEGER.UNSIGNED,
                    primaryKey: true,
                    allowNull: false,
                    autoIncrement: true,
                },
                orderId: {
                    type: Sequelize.INTEGER,
                    allowNull: false,
                },
                productName: {
                    type: Sequelize.STRING,
                    primaryKey: true,
                    allowNull: false,
                },
                count: {
                    type: Sequelize.INTEGER,
                },
                orderDetailPrice: {
                    type: Sequelize.INTEGER,
                    allowNull: false,
                },
                createdAt: {
                    allowNull: false,
                    type: Sequelize.DATE,
                },
                updatedAt: {
                    allowNull: false,
                    type: Sequelize.DATE,
                },
            })
                .then(() => {
                    return queryInterface.sequelize.query('ALTER TABLE `OrderDetails` ADD ' +
                        'CONSTRAINT `fk_order_details_orders` FOREIGN KEY (`orderId`, `orderDetailPrice`) REFERENCES ' +
                        'Orders(`orderId`, `totalPrice`)');
                });
        },
        down: (queryInterface, Sequelize) => {
            return queryInterface.dropTable('OrderDetails', null);
        },
    };