Question 113772 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Knex migration foreign key constraint does not exist (referenced table does exist?)

Looked at some of the other posts asking about the error ‘column "column_name" referenced in foreign key constraint does not exist’ but none of the answers/situations seem to match exactly:

Currently have two migration files:

20210123122225_createReservationsTable.js

exports.up = function (knex) {
  return knex.schema.createTable("reservations", (table) => {
    table.increments("reservation_id").primary();
    table.string("first_name").notNullable();
    table.string("last_name").notNullable();
    table.string("mobile_number").notNullable();
    table.date("reservation_date").notNullable();
    table.time("reservation_time").notNullable();
    table.integer("people");
    
    table.timestamps(true, true);
  });
};

exports.down = function (knex) {
  return knex.schema.dropTable("reservations");
};

20221112222830_tables.js


exports.up = function(knex) {
    return knex.schema.createTable("tables", (table) => {
        table.increments("table_id").primary();
        table.string("table_name").notNullable();
        table.integer("capacity").unsigned().notNullable();
        table
            .foreign("reservation_id")
            .references("reservation_id")
            .inTable("reservations")
            .onDelete("cascade");
        table.timestamps(true, true);
      })
};

exports.down = function(knex) {
  return knex.schema.dropTable("tables");
};

Running the migration results in:

migration file "20210123122225_createReservationsTable.js" failed
migration failed with error: alter table "tables" add constraint "tables_reservation_id_foreign" foreign key ("reservation_id") references "reservations" ("reservation_id") on delete cascade – column "reservation_id" referenced in foreign key constraint does not exist

I figured maybe the "reservations" table was being made after the "tables" table. But from what I understand, the migrations are run in order of the file names. Given that the "reservations" table’s migration file is dated earlier, it should be running first. I’ve also done this exact same method of migrations before and didn’t have an issue.

Either way, I tried running the migration for the "reservations" table first, and then the "tables" table migration separately. Same issue.

I tried other suggestions in previously asked questions here, like placing both table creations in the same file, using async tags to avoid race conditions, and still the same error.

Starting to feel like I’m missing something really obvious.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Of course I figure it out five minutes after I post the question, after an hour of going in circles.

    I simply forgot to declare the actual column for the foreign key!

    exports.up = function(knex) {
    return knex.schema.createTable("tables", (table) => {
        table.increments("table_id").primary();
        table.string("table_name").notNullable();
        table.integer("capacity").unsigned().notNullable();
        **table.integer("reservation_id").unsigned();**
        table
            .foreign("reservation_id")
            .references("reservation_id")
            .inTable("reservations")
            .onDelete("cascade");
        table.timestamps(true, true);
      })
};

exports.down = function(knex) {
  return knex.schema.dropTable("tables");
};

  2. 0

    The .down action should mention "cascade" in the drop.

    Dropping reservations needs to drop tables,
    or at a minimum drop its FK dependency.

    You have a …25…reservations file, and a …30.tables file.

    I’m unclear on what the state of your database is when
    the …25…reservations .up action throws a FK error.
    Both of us would expect it to
    (A.) not care about tables, and
    (B.) run fine, independent of whether tables exists.

    Maybe there’s a BEGIN … COMMIT transaction surrounding some of
    the ALTER statements?

    To debug this,

    1. Log what’s being run.
    2. Reproduce the symptom manually from a psql> prompt.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search