sequelize错误:缺少约束的索引

qco9c6ql  于 2021-06-18  发布在  Mysql
关注(0)|答案(2)|浏览(355)

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创建订单

'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');
  }
};

当我执行脚本时 sequelize db:migrate ,以前的迁移没有执行错误。在这个级别,它返回错误。我不知道该怎么解决这个问题我想它出了问题。
错误:添加外键约束失败。引用表“orders”中缺少约束“orderdetails\u ibfk\u 2”的索引
这是错误消息。我想连接文件 OrderDetails.orderDetailPrice 以及 Orders.totalPrice .
谢谢。

hrirmatl

hrirmatl1#

乔达诺,
我这样定义代码如下。让我知道为什么这个代码可以迁移?主键和唯一键这两个键都是写的。。
创建-order-detail.js

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('OrderDetails', {
          (...),
          productName: {
            type: Sequelize.STRING,
            primaryKey: true,
            // allowNull: false
            unique: true
          },
          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
          }
        })
      .then(() => {
        queryInterface.addConstraint('OrderDetails', ['orderDetailPrice'], {
          type: 'foreign key',
          references: {
            name: 'orderdetails_ibfk_2',
            table: 'Orders',
            field: 'totalPrice'
          },
        })
      })
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('OrderDetails');
  }
};
qvtsj1bj

qvtsj1bj2#

如本文所述,sequelize在引用复合键时似乎存在一些问题。
然而,通过利用sequelize查询执行,您可以解决您的问题。在这种情况下,您可以执行以下mysql查询:

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

因此,您的“创建订单明细”迁移文件将变为以下内容:

'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);
        },
    };

相关问题