CODEWITHSUNDEEP
node.jspostgresqlormsequelize.js
apollo
apollo

Reputation: 195

Postresql, Node.js ORM - foreignKey and primaryKey issue in relation table (Many to Many)

I am just creating Company and Industry table in many-to-many relation. A company can contain multiple industries and one industry can contain multiple countries. In UI, like multi-select box for company where I can choose several industries.

What I am doing in models:

Industry.js

'use strict';
module.exports = (sequelize, DataTypes) => {
  const Industry = sequelize.define('Industry', {
    industry_name: DataTypes.STRING,
  }, {
    timestamps: false,
    underscored: true,
    tableName: 'industry',
  });
  Industry.associate = function(models) {
    Industry.belongsToMany(models.Company, {
      through: 'company_industry_relation', foreignkey: 'industry_id'
    });
  };
  return Industry;
};

Company.js

'use strict';
module.exports = (sequelize, DataTypes) => {
  const Company = sequelize.define('Company', {
    company_name: DataTypes.STRING,
  }, {
    timestamps: false,
    underscored: true,
    tableName: 'company',
  });
  Company.associate = function(models) {
    Company.belongsToMany(models.Industry, {
      through: 'company_industry_relation', foreignKey: 'company_id'
    });
  };
  return Company;
};

CompanyIndustryRelation.js

'use strict';
module.exports = (sequelize, DataTypes) => {
  const CompanyIndustryRelation = sequelize.define('CompanyIndustryRelation', {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
    },
  }, {
    timestamps: false,
    underscored: true,
    tableName: 'company_industry_relation',
  });
  CompanyIndustryRelation.associate = function(models) {
    CompanyIndustryRelation.belongsTo(models.Company, { foreignKey: 'company_id' });
    CompanyIndustryRelation.belongsTo(models.Industry, { foreignKey: 'industry_id' });
  };
  return CompanyIndustryRelation;
};

But what I am concerned here is those models are creating company_id(PK) and industry_id(PK) in company_industry_relation table. Also id(PK) is not created in the table. In my opinion those two columns (company_id and industry_id) should be foreign keys and id(PK) should be created differing to those two columns?

Could anyone have an idea about this? Thanks in advance!

Upvotes: 0

Views: 32

Answers (1)

Greg Belyea
Greg Belyea

Reputation: 878

You do not need the associations in your relation table... just remove them... setting up either side of the belongsToMany is enough...

Your primary key in the relation table is a compound primary key... that means a company can belong to many industries, and a industry may have many companies but you can never duplicate the compound primary key... 

company_id         industry_id
1                  1
1                  2
2                  2
2                  3

These are all valid records in your join table, and the combination of them make up the primary key of the record... so obviously once 1,1 is used, then you can't nor would you want to add it again type thing... if you included an actual id field in that primary key then you or someone else working in your code could accidentally add that same relation again

Upvotes: 1

Related Questions