CODEWITHSUNDEEP
mysqlnode.jssequelize.js
Aman Kush
Aman Kush

Reputation: 91

Unhandled Rejection SequelizeDatabaseError : Foreign Key constraint error : cannot add foreign key

var state = require('./state')
module.exports = function (sequelize, DataTypes) {
    var city = sequelize.define('city', {
        city_id : {
            type : DataTypes.INTEGER,
            autoIncrement : true,
            primaryKey : true
        },
        city_name : {
            type : DataTypes.STRING,
            allowNull : false,
            unique : true
        },
        city_state_id : {
            type : DataTypes.INTEGER,
            allowNull : false,
            references : {
                model : 'states',
                key : 'state_id'
            }
        }
    }
    return  city;
}

This is my referencing file .

module.exports = function (sequelize, DataTypes) {
    var state = sequelize.define('state', {
        state_id: {
            type: DataTypes.INTEGER,
            autoIncrement: true,
            primaryKey: true
        },
        state_name: {
            type: DataTypes.STRING,
            allowNull: false,
            unique: true
        },
    })
    return state;
}

I do not know why I am not able to add foreign key constraint here. Can anybody help me with that? I am getting an error like this :

Unhandled rejection SequelizeDatabaseError: ER_CANNOT_ADD_FOREIGN: Cannot add foreign key constraint When i try to execute migration.js on terminal : node migration.js

migration.js : 

 /*jslint node: true */
 'use strict';
 var Sequelize = require('sequelize');
 var fs = require('fs');
 var schema_dir = './schema/';
 var config = require('./config').mysql_conf;
 var connection = new Sequelize(config.database, config.username, config.password, config);
 fs.readdir(schema_dir, function(err, files) {
     if (err) {
         console.log(err);
     } else {
         files = files.sort();
         console.log(files);
         for (var i = 0; i < files.length; i++) {
             var module_name = files[i].slice(0, -3);
             console.log(schema_dir + files[i]);
             //  var schema = require(schema_dir + module_name);
             try {
                 var model = connection['import']('' + schema_dir + module_name);
                 model.sync().then(function() {
                     console.log("table created: " + module_name);
                 });
                 model.sync({force : true})
             } catch (ex) {
                 console.log("error with module_name: " + module_name, ex);
             }
         }
     }

 });

Anybody has an idea why this is happening?

Upvotes: 1

Views: 4093

Answers (1)

Taku
Taku

Reputation: 5927

Several things that needs to be understood/fixed to make sure that this works properly.

sync() vs sync({force: true})

sync() will run CREATE TABLE IF NOT EXISTS whereas sync({force: true}) will run DROP TABLE IF EXISTS and then creates the table again. So, this migration method will not work for environments above local/dev. Also, if you want to create a column, change datatype etc, you cannot use these methods to apply your changes.

Use this to test locally and then create a migration file via SHOW CREATE TABLE and run those via Sequelize migration.

Asynchronous nature of sync

sync() is ironically asynchronous which means synchronous loop does not guarantee that each model is loaded in order. This can output different error logs and throw you off. You should use asynchronous handling modules like async:

async.eachSeries(files, function(file, callback) {
    var model = require(currentDir + '/' + file);
    model
        .sync({force: true})
        .then(function() {
            console.log('Force-synced %s', file);
            callback();
        })
        .catch(callback);
}, function(err) {
    if(err) throw err;
    console.log('Completed migration in order as such %o', files);
});

Order of migration files

You have file names like city.model.js and state.model.js. Since fs.readdir will read them in alphabetical order,

    city_state_id : {
        type : DataTypes.INTEGER,
        allowNull : false,
        references : {
            model : 'states',
            key : 'state_id'
        }
    }

cannot reference to table states since it does not exist yet.

You will need to order them properly. One method is to create an order array like this:

var order = [
    'state',
    'city'
];

async.eachSeries(order, function(file, callback) {
    var model = require(currentDir + '/' + file + '.model.js');
    model
        .sync({force: true})
        .then(function() {
            console.log('Force-synced %s', file);
            callback();
        })
        .catch(callback);
}, function(err) {
    if(err) throw err;
    console.log('Completed migration in order as such %o', files);
});

Others will not wait for the models to load

If you are running a test or server, they may start trying to access the models before they are loaded. The asynchronous nature of sync prevents the server to await for the models to load.

The solution is to use models as a way to query and not a way to change the structure of the table. This should be used to test locally and use Sequelize migration to make changes.

Upvotes: 3

Related Questions