How to update a constraint in a migration

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?

2 Likes

You have to drop it first and then recreate it, something in lines of:

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

Could also use this.schedule() to run multiple things
https://adonisjs.com/docs/4.1/migrations#_executing_arbitrary_code
Can delete first and then use this.alter for example

3 Likes