Sequelize - Identifier name 'xxx_idx' is too long when creating 1-to-N relationship

user_10686620 0
Viewed 663

I have 2 tables physical-assessment-exercise and psysical-assessment-lesson.

psysical-assessment-lesson.model.js

const Sequelize = require('sequelize');
const DataTypes = Sequelize.DataTypes;

module.exports = function (app) {
  const sequelizeClient = app.get('sequelizeClient');
  const result = sequelizeClient.define('physical_assessment_lesson', {
    ...
  });

  // eslint-disable-next-line no-unused-vars
  result.associate = function (models) {
    result.hasMany(models.psychological_assessment_answer);
  };

  return result;
};

physical-assessment-exercise.model.js

const Sequelize = require('sequelize');
const DataTypes = Sequelize.DataTypes;

module.exports = function (app) {
  const sequelizeClient = app.get('sequelizeClient');
  const exercise = sequelizeClient.define('physical_assessment_exercise', {
  ...
  });

  // eslint-disable-next-line no-unused-vars
  exercise.associate = function (models) {
    exercise.belongsTo(models.physical_assessment_lesson);
  };

  return exercise;
};

After I save above files, console shows below error:

{  "code":"ER_TOO_LONG_IDENT","errno":1059,"sqlState":"42000",
   "sqlMessage":
   "Identifier name 
   'physical_assessment_lesson_psychologicalAssessmentAnswerId_foreign_idx' is too long"
}

How should I fix this and customize the Identifier name?

sequelize.js node.js mysql sql
1 Answers

Mysql only allows 64 characters for naming its inde, as you can see here: https://dev.mysql.com/doc/refman/8.0/en/identifier-length.html

Your index-name currently contains 70 characters, so you have multiple options for the solution:

  1. You could rename your entites so that the name is not that long (e.g. "assessment" could be "ass", "physical" could be phy, "psychological" could be "psy" - don't forget to remember, what the short variants of these terms mean :-)

  2. Define the key instead of using the default-name, as seen here: https://sequelize.org/master/manual/indexes.html and specify a name, which is below 64 characters.

user_32842520
Related