How can I make a composite unique constraint with foreign keys and regular keys in Sequelize?

Question

We have two models, users and items. Under User.js

User = Model.define('User', {
    id: {
        type: DataType.UUID,
        defaultValue: DataType.UUIDV1,
        primaryKey: true,
    },
});

And under Item.js

Item = Model.define('Item', {
    id: {
        type: DataType.UUID,
        defaultValue: DataType.UUIDV1,
        primaryKey: true,
    },
});

Here is their association, a user can have many items.

User.hasMany(Items, {
    foreignKey: {
        allowNull: false,
        name: 'itemId',
    },
    onUpdate: 'cascade',
    onDelete: 'cascade',
});

Assume that each user may only have one of each type of item. How do I add a unique constraint for this? The following code does not work.

User.hasMany(Items, {
    foreignKey: {
        allowNull: false,
        name: 'itemId',
        unique: 'userItemUnique',
    },
    onUpdate: 'cascade',
    onDelete: 'cascade',
});

Item = Model.define('Item', {
    id: {
        type: DataType.UUID,
        defaultValue: DataType.UUIDV1,
        primaryKey: true,
        unique: 'userItemUnique',
    },
});

Answer 1

In my case I did something like this based on Joel Barenco's answer.

const { Model, DataTypes } = require('sequelize');
const User = require('../models/user');

module.exports = function(sequelize){
    class Algorithm extends Model {}
    UserModel = User(sequelize);//@JA - Gets a defined version of user class

    var AlgorithmFrame = Algorithm.init({
        id: {
            type: DataTypes.INTEGER,
            primaryKey: true,
            autoIncrement: true
        },
        name: {
            type: DataTypes.STRING,
            allowNull: false,
        },
        user_Id: {
            type: DataTypes.INTEGER,
            references: { 
                model: UserModel,
                key: 'id',
            },
        }
    }, {
      sequelize,
      modelName: 'Algorithm',
      indexes: [{ unique: true, fields: ['name','user_id'] }]
    });

    return AlgorithmFrame
};

The idea here is to manually create the foreign key, but you can define the unique indexes instead with indexes: [{ unique: true, fields: ['name','user_id'] }]

My tactic also shows how to define the model in a class as well. To call it you simply pass sequelize to it like this, where sequelize is the variable holding all your connection info etc...

const Algorithm = require('../models/algorithm');
const AlogorithmModel = Algorithm(sequelize);

then you can make sure it's created with

await AlogorithmModel.sync({ alter: true });

My user model file is this:

const { Model, DataTypes } = require('sequelize');
module.exports = function(sequelize){
    class User extends Model {}
    return User.init({
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      name: {
          type: DataTypes.STRING,
          allowNull: false
      },
      trading_system_key: {
          type: DataTypes.STRING,
          allowNull: false
      },
    }, {
      sequelize,
      modelName: 'User',
      indexes: [{ unique: true, fields: ['trading_system_key'] }]
    });
};

Answer 2

If anyone is still following this, I solved this by manually defining the foreign keys in the model where the unique constraint is required (you can still use sequelize association such as .hasMany).

Regarding your own code, I think there might be a confusion when you ask for Assume that each user may only have one of each type of item since you are not defining what is an item type.

I've drafted something with my own understanding and taking into account my previous comment.

User = Model.define('User', {
    id: {
        type: DataType.UUID,
        defaultValue: DataType.UUIDV1,
        primaryKey: true,
        allowNull: false,
        validate: {
          isUUID: 1,
        },
    },
});

Item = Model.define('Item', {
    id: {
        type: DataType.UUID,
        defaultValue: DataType.UUIDV1,
        primaryKey: true,
        allowNull: false,
        validate: {
          isUUID: 1,
        },
    },
    type: {
        type: DataType.STRING,
        unique: 'uniqueUserItemType' // see note 1
    }
    userId: { 
        type: DataType.UUID,
        references: { // see note 2
          model: User,
          key: 'id',
        },
        unique: 'uniqueUserItemType',
    }
});

User.hasMany(Item, {
    foreignKey: {
        allowNull: false,
        name: 'itemId',
    },
    onUpdate: 'cascade',
    onDelete: 'cascade',
});

Item.belongsTo(User);

I've also added a belongsTo association as recommended by Sequelize.

[1] More info on composite unique constraint here.

[2] More info on foreign key definition inside of model here.

Answer 3

You can use migrations for this.

Sequelize-cli provides a methods addConstraint and andIndex which can be used to achieve

From the docs

queryInterface.addConstraint('Users', ['email'],
 { type: 'unique', name: 'custom_unique_constraint_name'
 });