How to update a constraint in a migration

Tags: , , , ,



I need to add onDelete and onUpdate cascade to a constraint in a migration file.

So i did an alter table, selecting the foreign keys and chaining with the alter method in the end of each command.

class UpdateRecipientSchema extends Schema {
  up () {
    this.alter('deliveries', (table) => {
      table.integer('recipient_id')
        .unsigned()
        .references('id')
        .inTable('recipients')
        .onDelete('CASCADE')
        .onUpdate('CASCADE')
        .notNullable()
        .alter()
      table.integer('courier_id')
        .unsigned()
        .references('id')
        .inTable('couriers')
        .notNullable()
        .onDelete('CASCADE')
        .onUpdate('CASCADE')
        .alter()
    })
  }

  down () {
    this.alter('deliveries', (table) => {
      table.integer('recipient_id')
        .unsigned()
        .references('id')
        .inTable('recipients')
        .notNullable()
        .alter()

      table.integer('courier_id')
        .unsigned()
        .references('id')
        .inTable('couriers')
        .notNullable()
        .alter()
    })
  }
}

But i’m getting a error saying that the constraint of this relationship already exists. error: constraint "deliveries_courier_id_foreign" for relation "deliveries" already exists

How can i update a constraint of a table in a migration?

Answer

First i have to drop the foreign key column and then create it again.

We can do that with Arbitrary Code in migrations

await Database.raw('DROP FOREIGN KEY deliveries_courier_id_foreign')
// And now recreate it
await Database.raw('...')

We can also uso the this.schedule function to execute multiple things https://adonisjs.com/docs/4.1/migrations#_executing_arbitrary_code

Thanks to the guy that helped me in the Adonis forum https://forum.adonisjs.com/t/how-to-update-a-constraint-in-a-migration/5835



Source: stackoverflow