CODEWITHSUNDEEP
mysqldatabase-migrationknex.js
zkwsk
zkwsk

Reputation: 2116

Migration in Knex.js - cannot set foreign key on nullable field

I have a table where I currently have a required foreign key constraint. I need to make that constraint optional, so no constraint if the value is null and otherwise it should enforce the constraint.

I'm writing the migration in Knex.js and I have the following migration. Each of the 3 statements works in isolation, but if make the column nullable I cannot add the foreign key back in.

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

How can i make this column optional?

Upvotes: 1

Views: 2715

Answers (2)

Flo Schild
Flo Schild

Reputation: 5294

Seems pretty similar to 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();
  });
};

Upvotes: 1

zkwsk
zkwsk

Reputation: 2116

Eventually ended up using knex.raw():

exports.up = knex => {
  return knex.raw(
    "ALTER TABLE `waypoints` CHANGE `suggested_route_id` `suggested_route_id` INT(10) UNSIGNED NULL;"
  );
};

exports.down = knex => {
  return knex.raw(
    "ALTER TABLE `waypoints` CHANGE `suggested_route_id` `suggested_route_id` INT(10) UNSIGNED NOT NULL;"
  );
};

Could not find a way to do it via the Knex API.

Upvotes: 0

Related Questions