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.

CodePudding user response：

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");
};

CodePudding user response：

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.