Knex migration is failing when specifying a foreign key

Knex migration is failing when specifying a foreign key

Problem Description:

I am using Knex with NodeJS to build a schema, and I am recieving an error when trying to run my migration. There seems to be an issue with my foreign key that I specified in the vehicle table. Knex thinks that the data types between the keys are different, when they clearly aren’t. The database is running on Postgres.

Here is my current migration function.

export function up(knex) {
  return knex.schema
    .createSchemaIfNotExists("oem")
    .withSchema("oem")
    .createTable("ktm", function (table) {
      table.string("model");
      table.integer("year");
      table.integer("category");
      table.string("diagram");
      table.string("sku");
      table.string("title");
      table.index(["model", "year", "sku"]);
    })
    .createTable("vehicle_model", function (table) {
      table.uuid("id", { primaryKey: true });
      table.string("title");
    })
    .createTable("vehicle", function (table) {
      table.uuid("id", { primaryKey: true });
      table.string("handle").notNullable();
      table.uuid("vendor_id").notNullable();
      table
        .uuid("model_id")
        .notNullable()
        .references("id")
        .inTable("vehicle_model");
      table.integer("year").notNullable();
    });
}

Running this results in the following error message.

Key columns "model_id" and "id" are of incompatible types: uuid and integer.
error: alter table "oem"."vehicle" add constraint "vehicle_model_id_foreign" foreign key ("model_id") references "vehicle_model" ("id") - foreign key constraint "vehicle_model_id_foreign" cannot be implemented

Solution – 1

Found my issue.
Changed this:

.createTable("vehicle", function (table) {
  table.uuid("id", { primaryKey: true });
  table.string("handle").notNullable();
  table.uuid("vendor_id").notNullable();
  table
    .uuid("model_id")
    .notNullable()
    .references("id")
    .inTable("oem.vehicle_model"); // <<< specify schema before table
  table.integer("year").notNullable();
});