Ancestor unique key is too long

[SimonChaumet]

When synchronizing my model the ancestor database is created with a unique index with a name too long.

I'm using Sequelize v4.33.4, Node v10 minimum and MySQL 5.5.57.

Which parameter could be changed in order to get a shorter name ?

Server logs :

CREATE TABLE IF NOT EXISTS `TikcetCategoriesancestors` (`TikcetCategoryTicketCategoryId` INTEGER NOT NULL , `ancestorTicketCategoryId` INTEGER NOT NULL , UNIQUE `TikcetCategoriesancestors_TikcetCategoryTicketCategoryId_ancestorTicketCategoryId_unique` (`TikcetCategoryTicketCategoryId`, `ancestorTicketCategoryId`), PRIMARY KEY (`TikcetCategoryTicketCategoryId`, `ancestorTicketCategoryId`), FOREIGN KEY (`TikcetCategoryTicketCategoryId`) REFERENCES `ticket_category` (`ticketCategoryId`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`ancestorTicketCategoryId`) REFERENCES `ticket_category` (`ticketCategoryId`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
2020-02-12T14:09:06.538Z - error: Error while synchronizing with DB message=Identifier name 'TikcetCategoriesancestors_TikcetCategoryTicketCategoryId_ancestorTicketCategoryId_unique' is too long, stack=SequelizeDatabaseError: Identifier name 'TikcetCategoriesancestors_TikcetCategoryTicketCategoryId_ancestorTicketCategoryId_unique' is too long

My model :

const config = require('../config/config.js');

module.exports = function(sequelize, DataTypes) {
    const TikcetCategory = sequelize.define('TikcetCategory', {
        ticketCategoryId: {
            type: DataTypes.INTEGER,
            allowNull: false,
            primaryKey: true,
            autoIncrement: true
        },
        name: DataTypes.STRING(64),
    }, {
        tableName: 'ticket_category',
        hierarchy: true,
    });

    TikcetCategory.associate = function (models) {
    };

    return TikcetCategory;
};

Thanks for your help.

[overlookmotel]

Are you able to change the primary key field's name from ticketCategoryId to id? That'd likely solve it.

The naming of the index is determined by Sequelize itself, not this module.

If you don't want to change the field name, you could alternatively create the ancestors model yourself, and define the unique index yourself with whatever name you choose.

Hope that helps.

[SimonChaumet]

I like to have long id names but I changed to tcId instead and it seems to work.

But the throughName option is totally ignored (I use snake case for table names) :

const TicketCategory = sequelize.define('TicketCategory', {
        tcId: {
            type: DataTypes.INTEGER,
            allowNull: false,
            primaryKey: true,
            autoIncrement: true
        },
        name: DataTypes.STRING(64),
    }, {
        tableName: 'ticket_category',
        hierarchy: {
            throughName: 'ticket_category_ancestors'
        },
    });

[overlookmotel]

OK. Glad it worked. Like I said, Sequelize itself is what decides the names for indexes, so that's out of my control.

Concerning your other point, there is no throughName option. I think what you're after is throughTable. See the docs here.

I'm closing this issue as I think it's resolved (or at least as far as this module is able to resolve it). Shout if you have further problems.