Add foreign key reference to UUID Knex.js-CodePudding

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

CodePudding user response：

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.