sequelize: Composite index name too long

So I was creating a table tonight with 6 fields put into a compound index per the docs.

field: {type:Sequelize.STRING(34), unique:'main'},
another_field: {type:Sequelize.STRING(34), unique:'main'},
a_date_field: {type:Sequelize.STRING(34), unique:'main'},
yet_another_field: {type:Sequelize.STRING(34), unique:'main'},
almost_done_field: {type:Sequelize.STRING(34), unique:'main'},
last_field: {type:Sequelize.STRING(34), unique:'main'}

I’m getting an error from MySQL. ER_TOO_LONG_IDENT “Identifier name is too long”.

Sequelize is combining my field names to create an index name rather than using the ‘main’ string I’m providing.

Is there a way to provide the index name to avoid this issue?

About this issue

Original URL
State: closed
Created 10 years ago
Comments: 15 (3 by maintainers)

Most upvoted comments

Please reopen this. This is a bug in lib/associations/belongs-to-many.js, injectAttributes().

https://github.com/sequelize/sequelize/blob/f36db54dd5b031427fd8c52296c614c56421e4ba/lib/associations/belongs-to-many.js#L262

This line creates an index name for a BelongsToMany table, and if the name is longer than 64 characters it will fail on MySQL. On PostgreSQL I believe the name will be truncated to 63 chars, which also is not desirable.

Ideally I’d have the option to specify the index name myself.

natesilva on Jan 29, 2018

uniqueKey name can be overridden now https://github.com/sequelize/sequelize/pull/9914

sushantdhiman on Jul 27, 2019

During running sequelize migration I got

ERROR: Identifier name 'manufactured_products_downstream_manufacturing_value_id_foreign_idx' is too long

Is there any possibility to exceed that limitation?

migration.js

BartusZak on Jul 23, 2019

@nawlbergs

For M:M relations I found how to go around this problem. Create model for rm_userbizchallengeroadmaps and add keys (that will be foreign keys for connected tables):

roadmap_id: {
                type: Sequelize.INTEGER,
                unique: 'roadmap_id_userbizchallenge_id'
            }, 
userbizchallenge_id: {
                type: Sequelize.INTEGER,
                unique: 'roadmap_id_userbizchallenge_id'
            },

That unique key is a solution to use own key instead of generating one. Then, in settings through options in both connected models, set parmetr unique: false :

 FirstModel.belongsToMany(SecondModel, {
            through: {
                model: MiddleModel,
                unique: false
            }
        });

For me, this works. I only found a problem when I use parameter alter: true in sync option and then I have 1 : M relations. Same error with long identifier appears but I didn’t find way how to fix it except not enable ALTER parameter.

kareljuricka on Nov 1, 2017