Composite index name too long

[baliw]

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?

[mickhansen]

That is strange, it should definitely be using the string you provided.
We only define the name when we do a composite unique index on join tables (AFAIR).
Can you show create table SQL statement?

A PR with a failing unit test is as always welcome.

[baliw]

I'll look through the unit tests and see if I can put something together.

[thatisuday]

@mickhansen Yes, I happened to have the same problem. Composite index string is now being used for belongsToMany composition.

[nawlbergs]

Same... i have 3 tables.
rm_roadmaps
rm_userbizchallenges
and the through table: rm_userbizchallengeroadmaps

Error: ER_TOO_LONG_IDENT: Identifier name 'rm_userbizchallengeroadmaps_userBizChallenge_id_roadmap_id_unique' is too long

I cannot get it to generate...

[kareljuricka]

@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.

[natesilva]

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

sequelize/lib/associations/belongs-to-many.js

Line 262 in f36db54

const uniqueKey = [this.through.model.tableName, this.foreignKey, this.otherKey, 'unique'].join('_');

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.

[BartusZak]

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

[sushantdhiman]

uniqueKey name can be overridden now #9914