Cannot Add Foreign Key Constraint

[node-monk]

When running a migration with the below code, I get an "Cannot add foreign key contraint" message. Anyone point me in the right direction?

exports.up = function(knex, Promise) {
    var defer=Promise.defer();

    knex.schema.createTable('User',function(table){

        //--Create User Table
        table.increments('UserId').primary();
        table.string('username');
        table.string('email',60);
        table.string('password',65);
        table.timestamps();

    })
    .then(function(){
        return knex.schema.createTable('Comment',function(table){

            //--Create Comment Table
            table.increments('CommentId').primary();
            table.string('Comment');

            table.integer('UserId',11).inTable('User').references('UserId');

        });     
    })
    .then(function(){

            defer.resolve();
    });


    return defer.promise;

};

exports.down = function(knex, Promise) {

};  

[tgriesser]

Assuming you might be using mysql, you need to specify that the UserId is .unsigned()

You also shouldn't need that defer there, you can just return the knex.schema.createTable.

[bendrucker]

exports.up = function(knex, Promise) {
  return knex.schema.createTable('User',function (table){ 
      table.increments('UserId').primary();
      table.string('username');
      table.string('email',60);
      table.string('password',65);
      table.timestamps();
  })
  .then(function () {
    return knex.schema.createTable('Comment',function(table){
      table.increments('CommentId').primary();
      table.string('Comment');
      table.integer('UserId',11).unsigned().inTable('User').references('UserId');
    });     
  });
};

You'll almost never need Promise.defer.

[node-monk]

Wow. You guys ROCK!! Worked perfectly. Thank you for the quick response and the tips on cleaner code :)

[bendrucker]

You got it! You may find it easier to just split data/pk fields and fk references into two migrations. The alternative is what you've done here, but keep in mind that you then need to ensure order on the rollbacks as well.

[tgriesser]

In the version coming soon you'll be able to chain all schema calls and they'll be guaranteed to run in sequence:

exports.up = function(knex, Promise) {
  return knex.schema.createTable('User', function(table){ 
      table.increments('UserId').primary();
      table.string('username');
      table.string('email',60);
      table.string('password',65);
      table.timestamps();
  }).createTable('Comment',function(table){
      table.increments('CommentId').primary();
      table.string('Comment');
      table.integer('UserId',11).unsigned().inTable('User').references('UserId');  
  });
};

migrating down:

exports.down = function(knex, Promise) {
  return knex.schema.dropTable('Comment').dropTable('User');
};

[kamronbatman]

As an FYI to future people reading this:
table.integer('UserId',11).unsigned().inTable('User').references('UserId');
is now
table.integer('UserId',11).unsigned().references('UserId').inTable('User');

[jmichelin]

Thank you @batman!

[JonathanCMitchell]

Having errors with notNullable(). My foreign keys are always null:
EX 1:) table.integer('restaurant_id').unsigned().references('id').inTable('Restaurants');
The foreign key('restaurant_id") does not match the 'id' in the table 'Restaurants'. It is simply null, when I attempted to use :
EX2: ) table.integer('restaurant_id').unsigned().notNullable().references('id').inTable('Restaurants');
which threw an Error (cannot add foreign key constraints).

I have seen foreign key creation of this form work if that foreign key is a string, but with an integer referencing the primary key('id). Is it because that Primary key('id') in the parent is not of type integer and is of a default type?
table.increments('id').primary();

I have seen foreign keys work when they are a String type, but having an integer type reference to the primary key ('id') in another table throws an error.

When I use EX1) Everything appears to work, but my foreign key does not auto increment, it is simply null the entire time. I also cannot manually increment it using a model function. Did this issue get resolved somewhere?

Thanks,

update:
Figure it out, you simply have to add the integer column on a separate line from where you make it a foreign key.

table.integer('restaurant_id').unsigned();
          table.integer('location_id').unsigned();
          table.foreign('restaurant_id').references('Restaurants.id');
          table.foreign('location_id').references('Locations.id');

[Juanpam]

It may be worth clarifying in the docs that the .unsigned() method should be used when defining foreign keys in MySQL. I tried using this with Objection.js and almost gave up because I was only starting and got an error just at first glance. Thought both frameworks were buggy.

[elhigu]

Thought both frameworks were buggy.

@Juanpam no need to use them. PRs are welcome though. Good luck :)

[Juanpam]

@elhigu I didn't mean to offend anybody! I'm indeed using the framework 😄. Just thought that for a newcomer, like me, it may be a common problem since relations are a feature commonly used in ORMs and Query builders. Also, MySQL is a common RDBMS.

I can't do a PR right now, but if anyone is interested, I was trying to use XAMPP MySQL while creating the foreign keys. Turns out that when using the .primary() method the created field type is INT(11) unsigned and when you create the reference field using .integer() the resulting field type is INT(11) signed so the foreign key creation fails. @tgriesser fix works wonders, but it would be even better if you could find common fixes/problems like these in the docs.

Anyway, great work!