Having trouble with knex migrate:latest. Here's the error:
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
KNEX & MYSQL - Error: ER_CANNOT_ADD_FOREIGN: Cannot add foreign key constraint.
Unfortunately, the solution offered there did not work for my case...
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 Issue:
import * as Knex from "knex";
export async function up(knex: Knex): Promise<any> {
return knex.schema.createTable('personal_todos', (table)=>{
table.increments('id').primary();
// Tried unsigned, didn't work
table.integer('user_id').unsigned().notNullable();
table.integer('board_id').unsigned().notNullable();
table.foreign('user_id').references('users.id');
// The problematic line below causing error
// 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');
}
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')
}