How to modify foreign key on migration?

Hello there:

I have two entities: Lesson and LessonQuestion.

In LessonQuestion I have a foreignKey :

 table
        .integer('lesson_id')
        .unsigned()
        .notNullable()
        .references('id')
        .inTable('lessons')
        .onUpdate('CASCADE')

When I try to remove a “Lesson” it shows:

error: delete from “lessons” where “id” = $1 - update or delete on table “lessons” violates foreign key constraint “lesson_questions_lesson_id_foreign” on table “lesson_questions”

So I try to add a .onDelete(‘CASCADE’) in my previous migration:

If I run only:

up() {
    this.alter('lesson_questions', (table) => {
      // alter table
      table
        .integer('lesson_id')
        .onDelete('SET NULL')
        .alter()

It says:

TypeError: table.integer(…).onDelete is not a function

And if I try the full migration version:

class LessonQuestionsLessonIdForeignSchema extends Schema {
  up() {
    this.alter('lesson_questions', (table) => {
      // alter table
      table
        .integer('lesson_id')
        .unsigned()
        .notNullable()
        .references('id')
        .inTable('lessons')
        .onUpdate('CASCADE')
        .onDelete('SET NULL')
        .alter()
    })

It says :

error: constraint “lesson_questions_lesson_id_foreign” for relation “lesson_questions” already exists

So, how do I add a .onDelete(‘CASCADE’) to my lesson_id foreignKey ? Thanks.

You need to drop the foreign relationship first. Then alter the table. Do it in the same up method in your new migration file.

class LessonQuestionsLessonIdForeignSchema extends Schema {
  up() {
    this.alter('lesson_questions', (table) => {
      // drop existing foreign relationship on column 'lession_id'
      table.dropForeign('lesson_id')

      // alter table
      table
        .integer('lesson_id')
        .unsigned()
        .notNullable()
        .references('id')
        .inTable('lessons')
        .onUpdate('CASCADE')
        .onDelete('SET NULL')
        .alter()
      
    })
  }
}
1 Like

Impressive codes that is what i am seeking out for the past 2years haha. thanks so much!!