sequelize: Duplicated unique constraints and indexes on sequelize.sync({ alter: true })

Issue Description

When there is the unique: true property for any model field, sequelize creates a new unique constraint and index on each sequelize.sync({ alter: true })

What are you doing?

Create any model with a unique field like that:

  public initModel({ sequelize }: { sequelize: Sequelize }): ModelCtor<ExampleModel> {
    const exampleModel = sequelize.define<ExampleModel>(
      "ExampleModel",
      {
        id: {
          allowNull: false,
          defaultValue: DataTypes.UUIDV1,
          primaryKey: true,
          type: DataTypes.UUID,
        },
        exampleField: {
          unique: true,
          allowNull: false,
          type: DataTypes.STRING,
        },
    );
    return exampleModel;
  }

Then call sequelize.sync({ alter: true }).

What do you expect to happen?

Only single unique constraint and index are created after the each sequelize.sync({ alter: true }) call

What is actually happening?

For each sequelize.sync({ alter: true }) call there will be a new constraint named ExampleModel_exampleField_key1 (the next will be ExampleModel_exampleField_key2 and so on) and a new index called the same.

Environment

Sequelize version: 6.3.5
Node.js version: 14.15.0
Operating System: WSL2 Ubuntu 20.04
If TypeScript related: TypeScript version: 4.0.2

Issue Template Checklist

How does this problem relate to dialects?

I think this problem happens regardless of the dialect.
I think this problem happens only for the following dialect(s):
I don’t know, I was using PostgreSQL, with connector library version 8.3.3 and database version 12.4

Would you be willing to resolve this issue by submitting a Pull Request?

Yes, I have the time and I know how to start.
Yes, I have the time but I don’t know how to start, I would need guidance.
No, I don’t have the time, although I believe I could do it if I had the time…
No, I don’t have the time and I wouldn’t even know how to start.

About this issue

Original URL
State: closed
Created 4 years ago
Reactions: 30
Comments: 32 (4 by maintainers)

Commits related to this issue

fix: Corrigindo duplicidade de índices (bug do Sequelize) # Refêrencia: https://github.com/sequelize/sequelize/issues/12889#issuecomment-881786561 — committed to codecon-dev/app-site by galenodemelo 2 years ago

Most upvoted comments

I found a solution, erase the unique:true from the attributes and declare this indexes: [{ unique: true, fields: ["user"] }] in model options

+21

ingrocha on Jul 22, 2021

facing same issue today which exhaust table default indices

Error: Too many keys specified; max 64 keys allowed

My workaround: remove all duplicate indices for now by using given below query in MySQL db. ALTER TABLE table_name DROP INDEX phone_2, – phone_2 is index name DROP INDEX phone_3 – phone_3 is index name ;

abtisam on May 29, 2021

Facing the same issue with dialect Postgres. I am using Postgres 13. Any workaround would be much appreciated.

sandy912 on Jan 21, 2021

My solution : the issue was in my code sequelize file was being called multiple times. That is why it was putting constraint fine for the first time but second time it was throwing error. I fixed my code structure all went ok.

I am using sequelize with NestJS. I was adding database provider in every module. Correct way is to add it in app module and then import database module to the modules where you need

dilpazir-whizpool on Jun 10, 2022

The workaround does not seem to work for me. Possibly because I am using a composite index with SQLite.

Here is my model:

sequelize.define('foo', {
  // Model attributes are defined here
  A: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: 'someIndex'
  },
  B: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: 'someIndex'
  },
  C: {
    type: DataTypes.STRING,
    unique: 'someIndex'
  }
}, {
  indexes: [
    {
      unique: true,
      fields: ['A', 'B', 'C'],
      name: 'someIndex'
    }
  ]
})

Synchronizing with alter: true gives the error

ValidationErrorItem {
  message: 'B must be unique',
  type: 'unique violation',
  path: 'B',
  value: null,
  origin: 'DB',
  instance: null,
  validatorKey: 'not_unique',
  validatorName: null,
  validatorArgs: []
}

Seblor on Dec 28, 2021

Hello, faced same issue (but with mysql db), the workaround consisting of replacing unique: true in model’s field config by indexes: [{ unique: true, fields: ["YOUR_FIELD_NAME_HERE"] }] worked for me ! No more duplicate keys on sequelize sync (alter).

gaeldumon on Dec 15, 2021

Facing the same issue.

LucasRosaWP on May 26, 2021

Facing the same issue. Postgres.

FuketsuBaka on May 12, 2021

Facing the same issue.

gkourpa on Mar 21, 2021

Facing the same issue here too.

tiagowippel on Feb 23, 2021

Facing the same issue.

yusufkhan07 on Dec 5, 2020