CODEWITHSUNDEEP
knex.js
Obvious_Grapefruit
Obvious_Grapefruit

Reputation: 870

Knex.js - Foreign key constraint is incorrectly formed

I'm trying to build database migrations with the Knex.js query builder, however I get the following error relating to the foreign key relationship:

Knex:warning - migrations failed with error: alter table `presentations` add constraint presentations_owner_id_foreign foreign key (`owner_id`) references `users` (`user_id`) - ER_CANT_CREATE_TABLE: Can't create table `application`.`#sql-35c_6a` (errno: 150 "Foreign key constraint is incorrectly formed")

This is the migration for the presentations table:

exports.up = function(knex, Promise) {
  return Promise.all([
    knex.schema.createTable('presentations', function(table) {
      table.increments('presentation_id');
      table.string('title', 255);
      table.integer('owner_id');
      table.boolean('is_live');
      table.timestamps();

      table.foreign('owner_id').references('user_id').inTable('users');
    })
  ]);
};

And this is the migration for the users table:

exports.up = function(knex, Promise) {
  return Promise.all([
    knex.schema.createTable('users', function(table) {
      table.increments('user_id');
      table.string('first_name');
      table.string('last_name');
      table.string('email_address').unique();
      table.string('password');

      ...

      table.timestamps();
    })
  ]);
};

Could the differing data types be at fault? I am trying to establish a relationship between an integer and an increments type.

Upvotes: 3

Views: 2037

Answers (1)

abrain
abrain

Reputation: 490

The problem is that users.user_id and presentations.owner_id have different types.

// users table
table.increments('user_id'); // generates an unsigned integer

// presentations table
table.integer('owner_id'); // generates a signed integer

The solution is to make the column that references the primary key explicitly unsigned.

table.integer('owner_id').unsigned();

// or: create column and relation in one go
table.integer('owner_id').unsigned().notNullable().references('users.user_id');

See also: https://github.com/knex/knex/issues/245#issuecomment-619245125

By the way: SQLite either does not care or there are different types at play. We only discovered this later when we started testing against MariaDB.

Upvotes: 1

Related Questions