Add foreign key reference to UUID Knex.js

Question

I am using KnexJS to create a migration. Following are the migrations for the two table: Table Tasks

exports.up = function(knex) {
  return knex.schema.createTable('tasks', table => {
    table.uuid('id')
    table.string('title').notNullable()
    table.boolean('status').defaultTo(0)
    table.timestamps(true, true)
  })
};

exports.down = function(knex) {
  return knex.schema.dropTable('tasks')
};

Table: Subtasks

exports.up = function(knex) {
  return knex.schema.createTable('subtasks', table => {
    table.string('title').notNullable()
    table.boolean('status').defaultTo(0)
    table.uuid('task_id').references('id').inTable('tasks')
    table.timestamps(true, true)
  })
};

exports.down = function(knex) {
  return knex.schema.dropTable('subtasks')
};

But when running the migrations I get following error:

migration file "20211010072346_subtasks.js" failed
migration failed with error: alter table `subtasks` add constraint `subtasks_task_id_foreign` foreign key (`task_id`) references `tasks` (`id`) - ER_CANT_CREATE_TABLE: Can't create table `todo_application`.`subtasks` (errno: 150 "Foreign key constraint is incorrectly formed")

What am I doing wrong here?

Thank You

Answer 1

The referenced field "id" in the tasks table must be the same type and marked as the primary key of that table.

Since you want to use uuid - this seems to be possible in Mysql >= 8.

In your case and using Mysql >= 8 , you can use something like:

exports.up = function(knex) {
  return knex.schema.createTable("tasks", table => {
    table.uuid("id")
      .primary()
      .defaultTo(knex.raw("(UUID())"));
  });
};

If you cannot use an expression as default value (Mysql 5.7) - you will have to provide the uuid value in your client code.