How to choose name of Foreign Key column using Sequelize and mySql?

Question

I am using sequelize to model a mySql-database schema in my node-application. An extract of my model looks like this: I have a company-table and a department-table. A company can have multiple departments and a department belongs to only one company. I modeled this as follows:

The company-table:

module.exports = function(sequelize, DataTypes){
return Company = sequelize.define('Company', {
    companyId: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        allowNull: false,
        autoIncrement: true,
        unique: true            
    },
    name: {
        type: DataTypes.STRING,
        allowNull: false
    }
})}

The department-table:

var Company = require('./company');

module.exports = function(sequelize,DataTypes) {
return Department = sequelize.define('Department', {
    departmentId: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        allowNull: false,
        autoIncrement: true,
        unique: true
    },
    name: {
        type: DataTypes.STRING,
        allowNull: false            
    },
    companyId: {
        type:           DataTypes.INTEGER,
        references:     'Companies',
        referencesKey:  'companyId',
        onDelete:       'cascade'
    }
});}

To actually store this schema in the database I use the following code:

var db = require('../models/index');
db["Company"].hasMany(db["Department"], {as: 'departments'});
db["Department"].belongsTo(db["Company"], {foreignKey: 'companyId', foreignKeyConstraint: true});

models.sequelize.sync().complete(function(err){
    //irrelevant for the problem
});

The problem is that this code creates 2 foreign keys in the department table. One on the field "companyId" (as expected) but also one on the field "CompanyCompanyId", a field that is automatically generated.

How can I make sure that only the foreign key I defined ('companyId') is used and created?

Solution 1

I managed to fix the problem:

Instead of only using the "foreignKey"-option in the belongsTo-statement, it should also be used in the "hasMany"-statement.

The two models that were posted in the original question remained the same. The only thing I had to change was the location of the foreignKey option:

var db = require('../models/index');
db["Company"].hasMany(db["Department"], {as: 'departments'});
db["Department"].belongsTo(db["Company"], {foreignKey: 'companyId', foreignKeyConstraint: true});

changed to:

var db = require('../models/index');
db["Company"].hasMany(db["Department"], { foreignKey: 'companyId'});
db["Department"].belongsTo(db["Company"], {foreignKey: 'companyId'});

Solution 2

In the version 4.4.0, there is a targetKey option for the belongsTo function.

const User = this.sequelize.define('user', {/* attributes */})
const Company  = this.sequelize.define('company', {/* attributes */});

User.belongsTo(Company, {foreignKey: 'fk_companyname', targetKey: 'name'}); // Adds fk_companyname to User

more information on http://docs.sequelizejs.com/manual/tutorial/associations.html#target-keys

Solution 3

I know this thread is somewhat old, but I stumbled upon it when having problems declaring a 1:n association. I am using sequelize 5.21.5. The way to reference keys in the attributes changed from (borrowed from your example):

companyId: {
  type:           DataTypes.INTEGER,
  references:     'Companies',
  referencesKey:  'companyId'
}

to:

companyId: {
  type: DataTypes.INTEGER,
  references: {
    model: 'Company',
    key: 'companyId'
  }
}

Hope this helps any weary traveler along the way!