mysql - Knex.js 中的迁移 - 无法在可空字段上设置外键

Question

我有一个表，其中我目前有一个必需的外键约束。我需要将该约束设为可选，因此如果值为 null 则没有约束，否则它应该强制执行该约束。

我正在 Knex.js 中编写迁移，并且我有以下迁移。 3 个语句中的每一个都独立工作，但如果使列可为空，我将无法重新添加外键。

exports.up = knex => {
  return knex.schema
    // Statement 1
    .table("waypoints", table => {
      table.dropForeign("port_id");
    })
    // Statement 2
    .alterTable("waypoints", table => {
      table
        .integer("port_id")
        .nullable()
        .alter();
    })
    // Statement 3
    .table("waypoints", table => {
      table.foreign("port_id").references("port.id");
    });
};

如何使此列可选？

Answer 1

看起来与 https://github.com/knex/knex/issues/1218#issuecomment-650614036 非常相似

exports.up = knex => {
  return knex.schema.alterTable('waypoints', table => {
    table.integer('suggested_route_id').nullable().alter();
  });
};

exports.down = knex => {
  return knex.schema.alterTable('waypoints', table => {
    // CAVEAT: if you have waypoints records where `suggested_route_id` is `null`  when you run this, how should those be migrated to respect the non-nullable constraint?
    // One way is to manually assign a value before running the rollback migration
    // Or you could also remove the associated records depending on your use-case.
    table.integer('suggested_route_id').notNullable().alter();
  });
};