0

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')
    }

1 Answer 1

0

I figured out it was how the files were made. Knex builds each table in the order you did this command js knex migrate:make table_name.

This means that you must use the command in the order you are referencing. If you are going to reference another table make sure it's made before or you will have a foreign key restraint error. If you happen to run into this error then copy the table that was after and that has the foreign key and remove the old one (delete the file). Then make another one with the same name with the js knex migrate:make table_name command and paste in your old table. Do the same for seed if you are using one. make sure to rollback and drop and recreate your database!

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.