Knex: Unable to create foreign key

[Gwash3189]

Hi,

I was trying out knex and kept coming across an error when trying to make a foreign key to an auto incrementing field.

Here is my code

var knex = require('knex')({
  client: 'mysql',
  connection: {
    host: 'localhost',
    user: 'dungeonwriter',
    password: 'derp',
    database: 'dungeonwriter',
    debug: true
  }
});

knex.schema.dropTableIfExists('notes').then(() => {
  return knex.schema.dropTableIfExists('users');
}).then(() => {
  return knex.schema.createTableIfNotExists('users', (users) => {
    users.increments();
    users.string('email');
    users.string('password');
    users.timestamps();
    users.boolean('deleted')
      .defaultTo(false);
  })
}).then(() => {
  return knex.schema.createTableIfNotExists('notes', (notes) => {
    notes.increments();
    notes.timestamps();
    notes.integer('user_id')
      .references('id')
      .inTable('users');
  });
});

and here is the error i receive

<-- HandshakeInitializationPacket
HandshakeInitializationPacket {
  protocolVersion: 10,
  serverVersion: '5.6.27',
  threadId: 151,
  scrambleBuff1: <Buffer 33 79 2c 6b 56 4f 67 3b>,
  filler1: <Buffer 00>,
  serverCapabilities1: 63487,
  serverLanguage: 33,
  serverStatus: 2,
  serverCapabilities2: 32895,
  scrambleLength: 21,
  filler2: <Buffer 00 00 00 00 00 00 00 00 00 00>,
  scrambleBuff2: <Buffer 2f 77 2d 76 2e 7d 4f 50 27 6c 6c 5a>,
  filler3: <Buffer 00>,
  pluginData: 'mysql_native_password',
  protocol41: true }

--> ClientAuthenticationPacket
ClientAuthenticationPacket {
  clientFlags: 455631,
  maxPacketSize: 0,
  charsetNumber: 33,
  filler: undefined,
  user: 'dungeonwriter',
  scrambleBuff: <Buffer 13 cf af c1 78 7a 0d e7 52 30 b6 27 72 86 59 7b 33 01 4b cb>,
  database: 'dungeonwriter',
  protocol41: true }

<-- OkPacket
OkPacket {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }

<-- HandshakeInitializationPacket
HandshakeInitializationPacket {
  protocolVersion: 10,
  serverVersion: '5.6.27',
  threadId: 152,
  scrambleBuff1: <Buffer 79 28 58 63 69 67 47 6c>,
  filler1: <Buffer 00>,
  serverCapabilities1: 63487,
  serverLanguage: 33,
  serverStatus: 2,
  serverCapabilities2: 32895,
  scrambleLength: 21,
  filler2: <Buffer 00 00 00 00 00 00 00 00 00 00>,
  scrambleBuff2: <Buffer 6c 58 55 21 44 71 31 3a 70 49 59 65>,
  filler3: <Buffer 00>,
  pluginData: 'mysql_native_password',
  protocol41: true }

--> ClientAuthenticationPacket
ClientAuthenticationPacket {
  clientFlags: 455631,
  maxPacketSize: 0,
  charsetNumber: 33,
  filler: undefined,
  user: 'dungeonwriter',
  scrambleBuff: <Buffer d8 fa 2d 44 e4 4c 0c f6 70 70 08 5b 51 74 bc c6 f9 23 28 e5>,
  database: 'dungeonwriter',
  protocol41: true }

<-- OkPacket
OkPacket {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }

--> ComQueryPacket
ComQueryPacket { command: 3, sql: 'drop table if exists `notes`' }

<-- OkPacket
OkPacket {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }

--> ComQueryPacket
ComQueryPacket { command: 3, sql: 'drop table if exists `users`' }

<-- OkPacket
OkPacket {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  serverStatus: 2,
  warningCount: 1,
  message: '',
  protocol41: true,
  changedRows: 0 }

--> ComQueryPacket
ComQueryPacket {
  command: 3,
  sql: 'create table if not exists `users` (`id` int unsigned not null auto_increment primary key, `email` varchar(255), `password` varchar(255), `created_at` datetime, `updated_at` datetime, `deleted` boolean default \'0\')' }

<-- OkPacket
OkPacket {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }

--> ComQueryPacket
ComQueryPacket {
  command: 3,
  sql: 'create table if not exists `notes` (`id` int unsigned not null auto_increment primary key, `created_at` datetime, `updated_at` datetime, `user_id` int)' }

<-- OkPacket
OkPacket {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }

--> ComQueryPacket
ComQueryPacket {
  command: 3,
  sql: 'alter table `notes` add constraint notes_user_id_foreign foreign key (`user_id`) references `users` (`id`)' }

<-- ErrorPacket
ErrorPacket {
  fieldCount: 255,
  errno: 1215,
  sqlStateMarker: '#',
  sqlState: 'HY000',
  message: 'Cannot add foreign key constraint' }

Unhandled rejection Error: ER_CANNOT_ADD_FOREIGN: Cannot add foreign key constraint

I'm not sure if i'm doing something completely idiotic, or have stumbled upon a legitimate bug. Would i be able to get some guidance?

[Gwash3189]

Solved my own problem, maybe i didn't have enough morning coffee.

The solution change the definition of the user_id field from

notes.integer('user_id')
      .references('id')
      .inTable('users');

notes.integer('user_id')
      .unsigned()
      .notNullable()
      .references('id')
      .inTable('users');