Sequelize中间表OrdersEquipment字段异常及非空约束问题

10天前遇到以下问题，至今无解：在订单（orders）与借调设备（equipment）实体间建立了多对多关系，通过OrdersEquipment中间表记录各设备ID与订单的关联以实现严格管控，当前存在两个问题：

问题1：未赋值字段自动加载为null #

在forOf循环中根据对应Model更新fieldName时，未赋值的pdasId字段会被自动加载（值为null），控制台输出如下：

pcsId VAMOS BIEN OrdersEquipment {
  dataValues: { pdasId: null, orderId: 6, pcsId: 1 },
  _previousDataValues: { orderId: undefined, pcsId: undefined },
  uniqno: 1,
  _changed: Set(2) { 'orderId', 'pcsId' },
  _options: {
    isNewRecord: true,
    _schema: null,
    _schemaDelimiter: '',
    attributes: undefined,
    include: undefined,
    raw: undefined,
    silent: undefined
  },
  isNewRecord: true
}

问题2：allowNull设置无效导致报错 #

仅请求PDAs类型设备时，会报错："message": "the null value in the column ‘pdasId’ of the relationship ‘OrdersEquipments’ violates the constraint of not null"，即使已在OrdersEquipment模型中设置pdasId字段的allowNull: true仍无效。

尝试过修改模型关联关系和控制器逻辑，但问题依旧。以下是相关代码：

控制器代码 #

export const createOrder = async (req, res) => {
  try {
    const {
      responsibility,
      event,
      note,
      state,
      userId,
      pdaCount,
      handiesCount,
      pcsCount,
      printHardCount,
      printPocketCount,
      celusCount,
      complementsCount,
    } = req.body;

    const selectEquipment = async (model, count) => {
      const availableEquipments = await model.findAll({ where: { userId: 1 } });
      if (availableEquipments.length < count) {
        throw new Error(
          `No hay suficientes equipos disponibles de tipo ${model.name}`
        );
      }
      return availableEquipments.slice(0, count);
    };

    const selectedPDAs = await selectEquipment(PDAS, pdaCount);
    const selectedCelus = await selectEquipment(CELUS, celusCount);
    const selectedPCs = await selectEquipment(PCS, pcsCount);
    const selectedPrintHards = await selectEquipment(
      HARDTICKET,
      printHardCount
    );
    const selectedPrintPockets = await selectEquipment(
      POCKET,
      printPocketCount
    );
    const selectedHandies = await selectEquipment(HANDIES, handiesCount);
    const selectedComplements = await selectEquipment(
      COMPLEMENT,
      complementsCount
    );

    if (
      selectedPDAs.length === pdaCount &&
      selectedCelus.length === celusCount &&
      selectedPCs.length === pcsCount &&
      selectedPrintHards.length === printHardCount &&
      selectedPrintPockets.length === printPocketCount &&
      selectedHandies.length === handiesCount &&
      selectedComplements.length === complementsCount
    ) {
      const newOrder = await ORDERS.create({
        responsibility,
        event,
        note,
        state,
        userId,
      });

      const assignEquipmentsToOrder = async (equipments, model) => {
        for (const equipment of equipments) {
          console.log(model.name)
          const fieldName = `${model.name.toLowerCase()}Id`;

          const newOrderEquipment = {
            orderId: newOrder.id,
            [fieldName]: equipment.id,
          };
          await ordersEquipmentModel.beforeCreate((newOrderEquipment) => {
              if (newOrderEquipment[fieldName] != null) {
                console.log(fieldName, "VAMOS BIEN", newOrderEquipment);
              } else {
                console.log("VAMO MAL", newOrderEquipment);
              }
            });
          await ordersEquipmentModel.create(newOrderEquipment);

          equipment.userId = userId;
          await equipment.save();
        }
      };

      await assignEquipmentsToOrder(selectedPDAs, PDAS);
      await assignEquipmentsToOrder(selectedPCs, PCS);
      await assignEquipmentsToOrder(selectedPrintHards, HARDTICKET);
      await assignEquipmentsToOrder(selectedPrintPockets, POCKET);
      await assignEquipmentsToOrder(selectedCelus, CELUS);
      await assignEquipmentsToOrder(selectedHandies, HANDIES);
      await assignEquipmentsToOrder(selectedComplements, COMPLEMENT);

      return res.status(200).json(newOrder);
    } else {
      throw new Error("No se pudieron asignar todos los equipos solicitados.");
    }
  } catch (error) {
    return res.status(500).json({ message: error.message });
  }
};

模型代码 #

import { DataTypes } from "sequelize";
import { sequelize } from "../../../database/database.js";
import ORDERS from "../orders/ordersModel.js";
import PDAS from "../equipos/pdas.js";
import CELUS from "../equipos/celus.js";
import PCS from "../equipos/pc.js";
import HARDTICKET from "../equipos/printHard.js";
import POCKET from "../equipos/printPocket.js";
import HANDIES from "../equipos/handies.js";
import COMPLEMENTARY from "../equipos/complementary.js";

const ordersEquipmentModel = sequelize.define(
  "OrdersEquipment",
  {
    orderId: {
      type: DataTypes.INTEGER,
      references: {
        model: ORDERS,
        key: "id",
      },
      allowNull: false,
    },
    celusId: {
      type: DataTypes.INTEGER,
      references: {
        model: CELUS,
        key: "id",
      },
      allowNull: true,
    },
    pdasId: {
      type: DataTypes.INTEGER,
      references: {
        model: PDAS,
        key: "id",
      },
      allowNull: true,
    },
    pcsId: {
      type: DataTypes.INTEGER,
      references: {
        model: PCS,
        key: "id",
      },
      allowNull: true,
    },
    hardTicketId: {
      type: DataTypes.INTEGER,
      references: {
        model: HARDTICKET,
        key: "id",
      },
      allowNull: true,
    },
    pocketId: {
      type: DataTypes.INTEGER,
      references: {
        model: POCKET,
        key: "id",
      },
      allowNull: true,
    },
    handiesId: {
      type: DataTypes.INTEGER,
      references: {
        model: HANDIES,
        key: "id",
      },
      allowNull: true,
    },
    complementaryId: {
      type: DataTypes.INTEGER,
      references: {
        model: COMPLEMENTARY,
        key: "id",
      },
      allowNull: true,
    },
  },
  {
    timestamps: false,
  }
);

export default ordersEquipmentModel;

内容的提问来源于stack exchange，提问作者lautaro carrizo