Why do I have twice the foreign keys using Sequelize

Question

I'm trying to setup a simple blog application, I have the following schema definition:

Here is User:

module.exports = function(sequelize, DataTypes) {
    var User = sequelize.define("User", {
        id: {type: DataTypes.BIGINT, autoincrement:true, primaryKey: true},
        firstName: {type: DataTypes.STRING},
        lastName: {type: DataTypes.STRING},
        nickName: {type: DataTypes.STRING},
        email: {type: DataTypes.STRING, unique: true, comment: "Unique "},
        password: {type: DataTypes.STRING},
        salt: {type: DataTypes.STRING},
        enabled: {type: DataTypes.BOOLEAN, allowNull: false, defaultValue: true}
    }, {
        freezeTableName: true,
        classMethods: {
            associate: function (models) {
                User.hasMany(models.Article, {as: "Articles", constraints: false});
                User.hasMany(models.Comment, {as: "Comments", constraints: false});
            }
        }
    });

    return User;
};

Here is Article:

module.exports = function(sequelize, DataTypes) {
    var Article = sequelize.define("Article", {
        id: {type: DataTypes.BIGINT, autoincrement:true, primaryKey: true},
        slug: {type: DataTypes.STRING, comment: "Unique URL slug to access the article"},
        title: {type: DataTypes.STRING},
        content: {type: DataTypes.TEXT},
        created: {type: DataTypes.DATE, defaultValue: DataTypes.NOW},
        published: {type: DataTypes.BOOLEAN, allowNull: false, defaultValue: true}
    }, {
        freezeTableName: true,
        classMethods: {
            associate: function (models) {
                Article.belongsTo(models.User, {as: "Author", foreignKey: "author_id"});
                Article.hasMany(models.Comment, {as: "Comments", constraints: false});
            }
        }
    });

    return Article;
};

and Comment:

module.exports = function(sequelize, DataTypes) {
    var Comment = sequelize.define("Comment", {
        id: {type: DataTypes.BIGINT, autoincrement:true, primaryKey: true},
        content: {type: DataTypes.TEXT},
        status: {type: DataTypes.INTEGER, defaultValue: 1},
        author: {type: DataTypes.BIGINT},
        article: {type: DataTypes.BIGINT}
    }, {
        freezeTableName: true,
        classMethods: {
            associate: function (models) {
                Comment.hasOne(Comment, {as : "Parent", foreignKey: "parent_id"});
                Comment.belongsTo(models.User, {as: "Author", foreignKey: "author_id"});
                Comment.belongsTo(models.Article, {as: "Article", foreignKey: "article_id"});
            }
        }
    });

    return Comment;
};

The tables are created correctly but I end up with 2 foreign keys each time, for instance this is the Article table in MySQL:

'id','bigint(20)','NO','PRI','0',''
'slug','varchar(255)','YES','',NULL,''
'title','varchar(255)','YES','',NULL,''
'content','text','YES','',NULL,''
'created','datetime','YES','',NULL,''
'published','tinyint(1)','NO','','1',''
'createdAt','datetime','NO','',NULL,''
'updatedAt','datetime','NO','',NULL,''
'author_id','bigint(20)','YES','MUL',NULL,''
'UserId','bigint(20)','YES','',NULL,''

UserId == author_id

User Table:

'id','bigint(20)','NO','PRI','0',''
'firstName','varchar(255)','YES','',NULL,''
'lastName','varchar(255)','YES','',NULL,''
'nickName','varchar(255)','YES','',NULL,''
'email','varchar(255)','YES','UNI',NULL,''
'password','varchar(255)','YES','',NULL,''
'salt','varchar(255)','YES','',NULL,''
'enabled','tinyint(1)','NO','','1',''
'createdAt','datetime','NO','',NULL,''
'updatedAt','datetime','NO','',NULL,''

This table is correct (no foreign keys)

Comment:

'id','bigint(20)','NO','PRI','0',''
'content','text','YES','',NULL,''
'status','int(11)','YES','','1',''
'author','bigint(20)','YES','',NULL,''
'article','bigint(20)','YES','',NULL,''
'createdAt','datetime','NO','',NULL,''
'updatedAt','datetime','NO','',NULL,''
'ArticleId','bigint(20)','YES','',NULL,''
'parent_id','bigint(20)','YES','MUL',NULL,''
'author_id','bigint(20)','YES','MUL',NULL,''
'article_id','bigint(20)','YES','MUL',NULL,''
'UserId','bigint(20)','YES','',NULL,''

ArticleId == article_id and UserId == author_id

As you can see I have the version camel cased and the one I've specified. What did I miss?

** EDIT **

There is no constraints in the database for the camel case field: UserId and ArticleId but Sequelize created the fields in the tables.

Jan Aagaard Meier Jan Aagaard Meier · Accepted Answer · 2014-10-29T07:39:02

You need to add the foreign key on both sides of the relation, e.g:

User.hasMany(models.Article, {constraints: false, foreignKey: 'author_id'});

Why do I have twice the foreign keys using Sequelize

1 Answers