Sequelize unique constraint multiple columns

Sequelize Unique Constraint on Multiple Columns

Sequelize is an ORM (Object-Relational Mapping) library for Node.js that provides an easy way to interact with databases. When working with Sequelize, you might come across the need to enforce unique constraints on multiple columns in a table.

To set a unique constraint on multiple columns in Sequelize, you can use the unique property of a column’s validation options. Here’s an example:

const { Sequelize, Model, DataTypes } = require('sequelize');

const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'mysql'
});

class User extends Model {}

User.init({
  username: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: 'compositeIndex'
  },
  email: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: 'compositeIndex'
  }
}, {
  sequelize,
  modelName: 'User',
  indexes: [{
    unique: true,
    fields: ['username', 'email']
  }]
});

// Usage example:
(async () => {
  await sequelize.sync({ force: true }); // Sync the models with the database

  try {
    const user = await User.create({
      username: 'john',
      email: 'john@example.com'
    });

    console.log(user.toJSON());
  } catch (error) {
    console.error('Error creating user:', error);
  }
})();
  

In the example above, we define a User model with two columns: username and email. Both columns have the unique option set to ‘compositeIndex’, which means that they will be part of a composite index. Additionally, we define an index on the username and email columns that enforces uniqueness.

When creating a new user with the User.create method, Sequelize will automatically check if the username and email are unique. If a duplicate value is found, Sequelize will throw a validation error.

By using the unique constraint on multiple columns, you can ensure that specific combinations of values are unique in your table.