Cli: Unable to create composite unique constraint

[jharting]

If my model contains a composite unique constraint defined this way:

        system_id: {
            type: DataTypes.STRING,
            unique: 'action'
        },
        rule_id: {
            type: DataTypes.STRING,
            unique: 'action'
        },
        plan_id: {
            type: DataTypes.INTEGER,
            unique: 'action'
        }

then Sequelize adds the composite unique constraint properly. (UNIQUE action (system_id, rule_id, plan_id)).

If however I try to use the same construct in a migration file:

            system_id: {
                type: Sequelize.STRING,
                unique: 'action'
            },
            rule_id: {
                type: Sequelize.STRING,
                unique: 'action'
            },
            plan_id: {
                type: Sequelize.INTEGER,
                unique: 'action',
            }

no unique constraint is generated for the table.

Is this supposed to work or am I doing something wrong? I am using MariaDB.

[jharting]

One way to achieve what I need seems to be to add a unique index.

[jharting]

After digging through the code I found a way to add a composite unique constraint in a migration without adding an index:

queryInterface.createTable('actions', {
            id: {
                type: Sequelize.INTEGER,
                primaryKey: true,
                autoIncrement: true
            },
            system_id: {
                type: Sequelize.STRING,
            },
            rule_id: {
                type: Sequelize.STRING,
            },
            plan_id: {
                type: Sequelize.INTEGER,
                unique: 'actions_unique',
            }
        }, {
            uniqueKeys: {
                actions_unique: {
                    fields: ['system_id', 'rule_id', 'plan_id']
                }
            }
        });

[jharting]

I am going to send a PR later and move some of the logic from model.js to query-interface.js so that the same approach to defining composite unique constraints can be used in both model and migrations.

[Americas]

I do something similar:

            uniqueKeys : [{
                name        : "Unique value per currency",
                singleField : false,
                fields      : ["cuv_currency", "cuv_value"],
            }],

The changes you are proposing are for sequelize itself and not the cli. If you have no more questions, please close this issue.

[ecastano]

@Americas - Do you use that same code in migration and in the model definition under options?

[Americas]

No, in the model we have to use the unique property on each attribute.

[Bereket-G]

Define 'unique: true' attribute to each key

`key1: {
// needs to be unique
type: DataTypes.STRING,
allowNull: false,
unique: true
},

key2: {
// needs to be unique
type: DataTypes.STRING,
allowNull: false,
unique: true
}`

[notsag-dev]

I'm using Sequelize 4.38.0 and couldn't get any of the previous presented solutions working. Creating a new migration to add the unique constraint (instead of doing it along with the table creation) solved the problem:

module.exports = {
    up: (queryInterface, Sequelize) =>
        queryInterface.addConstraint('TableName', ['field1', 'field2'], {
            type: 'unique',
            name: 'indexname'
        }),
    down: queryInterface => queryInterface.removeConstraint('TableName', 'indexname')
};

[botaniQQQ]

@notsag-dev Thanks for the decision. It is strange why this is not in the documentation.
Can you explain one thing, when I add it to the migration file, can I somehow add this condition to the model file? Or do I need to manually change the model file? Or is there a way to create a model file from a migration file automatically?

{
    field1: {
        type: DataTypes.STRING,
        unique: 'indexname'
    },
    field2: {
        type: DataTypes.STRING,
        unique: 'indexname'
    },
}