Update foreign key constraint with new migration knexjs?

user_13128170 0
Viewed 373

I am trying to add an onUpdate onDelete cascade alteration to the foreign key in my migration. How can I do this?

original migration without cascading

exports.up = function(knex) {

    return knex.schema.createTable('invitations', (table) => {

        table.integer('eventId').unsigned().notNullable();
        table.foreign('eventId').references('id').inTable('events');

    });
};

exports.down = function(knex) {

    return knex.schema.dropTable('invitations');

};

new migration with cascading

exports.up = function(knex) {
    return knex.schema.alterTable('invitations', (table) => {

        table.foreign('eventId')
          .onUpdate('CASCADE')
          .onDelete('CASCADE')
            .alter();

    });
};

exports.down = function(knex) {

    return knex.schema.dropColumn('invitations');
  
};

new migration throws errors 'TypeError: table.foreign(...).onUpdate(...).onDelete(...).alter is not a function'. What would be the proper way to do this without rolling back the original migration?

knex.js node.js
1 Answers

As foreign key is a one off action, it does not support alter.

The simplest way:

  • Just drop the foreign key and create new one.
  • For rollback, drop the foreign key and create again with the old setting.
exports.up = function(knex) {
  return knex.schema.alterTable("invitations", (table) => {
    table.dropForeign("eventId");
    table
      .foreign("eventId")
      .references("events.id")
      .onDelete("CASCADE")
      .onUpdate("CASCADE");
  });
}

exports.down = function(knex) {
  return knex.schema.alterTable("invitations", (table) => {
    table.dropForeign("eventId");
    table
      .foreign("eventId")
      .references("events.id")
      .onDelete("NO ACTION")
      .onUpdate("NO ACTION");
  });
}
user_155694920
Related