KNEX & MYSQL - Error: ER_CANNOT_ADD_FOREIGN: Cannot add foreign key constraint

Question

I'm refactoring a monolith into microservices, and switching the DB of one of them to MySQL. I'm using knex.js to sanitize the queries. I need to build 3 tables. One of the tables only has three columns: its own id, and two foreign key ids: one from each of the other two tables.

When trying to build the knex.js query to build the tables, I get the error in the title.

I've attempted to re-arrange my queries using the various modifications that can be made to knex.js queries, as well as attempted a raw mysql foreign key query. The error persists. Here is my js code:

const knex = require('knex') (CONFIG);

// Build images table
const imagesSchema = () => {
  knex.schema.createTable('images', (table) => {
    table.integer('id').primary();
    table.string('name');
    table.string('src');
    table.string('alt');
    table.string ('category');
    table.string('subCategory');
  })
  .then(console.log('test'));
};

// Build users table
const usersSchema = () => {
  knex.schema.createTable('users', table => {
    table.increments('id').primary();
    table.string('session', 64).nullable();
  })
  .then(console.log('users schema built into DB!'))
  .catch( error => {console.log('cant build the users schema!\n', error)})
}

// Build userhistory table
const userHistorySchema = () => {
  knex.schema.createTable('userhistory', table => {
    table.increments('id').primary();
    table.integer('userid').nullable();
    table.integer('imageid').nullable();

    // add foreign keys:
    table.foreign('userid').references('users.id');
    table.foreign('imageid').references('images.id');
  })
  .then(console.log('userhistory schema built into DB!'))
  .catch( error => {console.log('cant build the userhistory schema!\n', error)})
}

I expect for the table to be created with the userhistory.userid column to point to the users.id column, and for the userhistory.imageid column to point to the images.id column. Instead, I receive this error:

Error: ER_CANNOT_ADD_FOREIGN: Cannot add foreign key constraint

code: 'ER_CANNOT_ADD_FOREIGN',
  errno: 1215,
  sqlMessage: 'Cannot add foreign key constraint',
  sqlState: 'HY000',
  index: 0,
  sql: 'alter table `userhistory` add constraint `userhistory_userid_foreign` foreign key (`userid`) references `users` (`id`)'

The tables are created without foreign keys where I would like for them to be.

Answer 1

For MySQL the foreign keys need to be defined as unsigned().
So your userhistory schema needs to be set up like this:

knex.schema.createTable('userhistory', table => {
    table.increments('id').primary();
    table.integer('userid').unsigned().nullable();
    table.integer('imageid').unsigned().nullable();

    // add foreign keys:
    table.foreign('userid').references('users.id');
    table.foreign('imageid').references('images.id');
  })
  .then(console.log('userhistory schema built into DB!'))
  .catch( error => {console.log('cant build the userhistory schema!\n', error)})
}

Answer 2

If you're creating tables with Knex, you should probably be using Knex's migration engine rather than rolling your own. However, your issue is that the two tables with primary keys haven't been created when you're trying to create the linking table. This is because you do not have the createTable method's callbacks nested.

I would rewrite this into something like this instead:

exports.up = async function (knex, Promise) => {

  await knex.schema.createTable('images', (table) => {
    table.integer('id').primary();
    table.string('name');
    table.string('src');
    table.string('alt');
    table.string ('category');
    table.string('subCategory');
  });

  await knex.schema.createTable('users', table => {
    table.increments('id').primary();
    table.string('session', 64).nullable();
  });

  await knex.schema.createTable('user_history', table => {
    table.increments('id').primary();
    table.integer('userid').nullable();
    table.integer('imageid').nullable();

    table.foreign('userid').references('users.id');
    table.foreign('imageid').references('images.id');
  });

};

exports.down = async function (Knex, Promise) => {
  await knex.dropTable('user_history');
  await knex.dropTable('images');
  await knex.dropTable('users');
};

Note that instead of using callbacks, I have switched to async/await syntax which makes this appear more like synchronous code. This should be easier to understand since the callbacks appeared to be tripping you up. In this example, when Node tries to run await knex.schema.createTable, it will pause and wait for the table to get created instead of jumping to the next statement. This will make sure that your images and users tables exist.

In order to do it this way, you'll have to use Knex's migration engine. If you're unfamiliar with migrations, check out this documentation.

Run these commands inside your project directory (where knexfile.js is):

1. npm install -g knex
2. knex migrate:make <your_migration_name>
3. knex migrate:latest

You should get a green console output saying something about how it successfully ran the migrations.