I am having an issue trying to knex migrate:latest I get this issue
migration file "20200701012817_personal_todos.ts" failed
migration failed with error: alter table `personal_todos` add constraint `personal_todos_board_id_foreign` foreign key (`board_id`) references `personal_todo_board` (`id`) on update CASCADE on delete CASCADE - ER_CANNOT_ADD_FOREIGN: Cannot add foreign key constraint
What I am trying to do is have a table that has two foreign keys that are from two different tables. So, there are the users and the todos tables which are where the foreign keys are going to be located. However, only the user foreign key works, and if I add in the other foreign key then I am given the error above. Any help would be appreciated! Also, knex is installed into node_modules... Here is the code, also it's not a similar question. The only one that is similar is this one
KNEX & MYSQL - Error: ER_CANNOT_ADD_FOREIGN: Cannot add foreign key constraint.
However, the answer doesn't work in my case sadly...
User table
import * as Knex from "knex";
export async function up(knex: Knex): Promise<any> {
return knex.schema.createTable('users', (table)=>{
table.increments('id').primary();
table.string('first_name');
table.string('last_name');
table.string('username').notNullable();
table.string('email');
table.string('password').notNullable();
table.integer('age');
table.string('bio');
table.string('gender');
table.string('personalsecret1');
table.string('personalsecret2');
table.string('personalsecret3');
table.binary('img');
table.string('colorScheme');
table.timestamps(true,true);
table.boolean('payed');
table.boolean('active');
table.boolean('friends_can_see_private');
})
}
export async function down(knex: Knex): Promise<any> {
return knex.schema.dropTableIfExists('users')
}
todos table is where the issue happens
import * as Knex from "knex";
export async function up(knex: Knex): Promise<any> {
return knex.schema.createTable('personal_todos', (table)=>{
table.increments('id').primary();
// I tried unsigned and it didn't work
table.integer('user_id').unsigned().notNullable();
table.integer('board_id').unsigned().notNullable();
table.foreign('user_id').references('users.id');
// here is the problem table.foreign('board_id').references('id').inTable('personal_todo_board').onUpdate('CASCADE').onDelete('CASCADE');
table.boolean('active').notNullable();
table.string('start_time');
table.string('end_time');
table.string('header');
table.string('body');
table.integer('container_index').notNullable();
table.integer('container_item_index');
table.timestamps(true,true);
table.boolean('private').notNullable();
})
}
export async function down(knex: Knex): Promise<any> {
return knex.schema.dropTableIfExists('personal_todos');
}
the board table
import * as Knex from "knex";
export async function up(knex: Knex): Promise<any> {
return knex.schema.createTable('personal_todo_board', (table)=>{
table.increments('id').primary();
table.integer('user_id').unsigned().notNullable();
table.foreign('user_id').references('users.id').onUpdate('CASCADE').onDelete('CASCADE');
table.string('header').notNullable();
table.string('last_active');
table.string('small_description');
table.timestamps( true, true);
table.boolean('private').notNullable();
})
}
export async function down(knex: Knex): Promise<any> {
return knex.schema.dropTableIfExists('personal_todo_board')
}