[unique: true] creates two unique indexes on a column

[wzup]

For some a reason column option unique: true creates two unique indexes on a column. How to prevent it? Please take a look at an email field:

const mysql = require('../getConnection').mysql; // configured and connected sequelize here

const modelAttrs = {
    fname: {
        type: mysql.Sequelize.STRING(255),
        field: 'fname',
        allowNull: true,
        defaultValue: null,
    },
    email: {
        type: mysql.Sequelize.STRING(254),
        field: 'email',
        allowNull: true,
        defaultValue: null,
        unique: true,      // <<== here an email column is unique
        validate: {
            isEmail: true,
        }
    },
    time_zone: {
        type: mysql.Sequelize.STRING(100),
        field: 'time_zone',
        allowNull: true,
        defaultValue: null,
    }
};

const modelOpts = {
    freezeTableName: true,
    tableName: 'UserProfile',
    timestamps: true,
}

const UserProfile = mysql.define('UserProfile', modelAttrs, modelOpts);

mysql.sync({ force: true });

And here is what is actually created, one unique key on email is redundant:

CREATE TABLE `userprofile` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fname` varchar(255) DEFAULT NULL,
  `lname` varchar(255) DEFAULT NULL,
  `full_name` varchar(500) DEFAULT NULL,
  `email` varchar(254) DEFAULT NULL,
  `accept_ts` tinyint(1) NOT NULL DEFAULT '0',
  `time_zone` varchar(100) DEFAULT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`), //      <<==  One
  UNIQUE KEY `UserProfile_email_unique` (`email`)  //     <<==  Two ??
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Why does it happen? How to prevent it?

[wzup]

UPD 1

Funny thing happens. When you delete unique: true from column definition and set it in Model options instead, unique index doesn't duplicate:

    email: {
        type: mysql.Sequelize.STRING(254),
        field: 'email',
        allowNull: true,
        defaultValue: null,
        // unique: true,      // comment out
        validate: {
            isEmail: true,
        }
    },

const modelOpts = {
    freezeTableName: true,
    tableName: 'UserProfile',
    timestamps: true,

    // Declare it here
    indexes: [
        {
          unique: true,
          fields: ['email']
        }
    ]
}

And the result is no redundant index:

CREATE TABLE `userprofile` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fname` varchar(255) DEFAULT NULL,
  `lname` varchar(255) DEFAULT NULL,
  `full_name` varchar(500) DEFAULT NULL,
  `email` varchar(254) DEFAULT NULL,
  `accept_ts` tinyint(1) NOT NULL DEFAULT '0',
  `time_zone` varchar(100) DEFAULT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_profile_email` (`email`)  // <<== only one !!
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Why? I don't want to move indexes to model options. I want to declare them in column definitions. How can I do it without duplications in results?

[janmeier]

Which version does this happen on?

[wzup]

Sequelize [Node: 5.9.1, CLI: 2.4.0, ORM: 3.23.3]

"version": "3.23.3"

[janmeier]

unique columns are added to uniqueKeys

sequelize/lib/model.js

Line 806 in 47b3f61

if (definition.hasOwnProperty('unique') && definition.unique !== false) {

- You are welcome to see if you can provide a fix for this, but I don't really see this as an issue

[nmarley]

I'm getting this too -- and I agree that it's an issue. There shouldn't be two unique indexes that are the exact same (only with different names) -- it's completely redundant.

nmarley$ node --version
v6.3.1
nmarley$ npm --version
3.10.3
nmarley$ npm view sequelize version
3.24.0

[sabakugaara]

I have this problem too

[kaidiren]

me too

[gzlock]

I have this problem too.
but i found the solution: use string instead of the true
example:

email: {unique: 'email'}

[broofa]

Had this same problem. @gzlock 's solution worked for me. I agree with others - it's disconcerting to see two nearly-identical indexes being created where you would expect to see one. Seems inefficient.

@janmeier - I haven't dived into the code to understand why you're comment about uniqueKeys implies this isn't an issue. Can you elaborate?

[janmeier]

@broofa Not being an issue is not specifically related to uniqueKeys, but just don't think this is a very serious bug ;)

The comment about uniqueKeys was just a pointer to anyone who wants to try and fix it

[naviapis]

+1

[AkivaLandsberg]

+1

[fcpauldiaz]

This still happens on version 3.30.4

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂

[jorrit]

This is still a bug, afaik.

[dashmug]

This is still a bug.

[hirenchauhan2]

This is still happening.

[psabharwal123]

still a bug

[iaptsiauri]

@janmeier is this still a bug? If so I will try to give it a shot, any tips where it is better to start?

[psabharwal123]

@iaptsiauri yes it is still a bug. you can try a simple example with unique true with mysql.

[Diluka]

still happen in latest version

And I found something maybe helpful

version "sequelize": "^4.20.1"
options sync: { alter: true, logging: true }

formatted sql for reading

1. using unique: true

CREATE TABLE IF NOT EXISTS `User` (
  `id`       INTEGER,
  `username` VARCHAR(255) UNIQUE,
  `password` VARCHAR(255),
  UNIQUE `User_username_unique` (`username`),
  PRIMARY KEY (`id`)
)
  ENGINE = InnoDB;

generated indexes: User_username_unique, username, username_2
every time add a new username_N

2. using unique: "User_username_unique"

CREATE TABLE IF NOT EXISTS `User` (
  `id`       INTEGER,
  `username` VARCHAR(255),
  `password` VARCHAR(255),
  UNIQUE `User_username_unique` (`username`),
  PRIMARY KEY (`id`)
)
  ENGINE = InnoDB;

generated indexes: User_username_unique
only one and will not create a new one when execute another time

If use unique: true, it will generate username VARCHAR(255) UNIQUE. This has no name and will create a new one each time.

[thecatontheflat]

Same here, using version 4.22.6

[x11joe]

This is happening for me also, exact issue as Diluka

[lostcodder]

I have same issue

[abelosorio]

I'm facing this issue but with a foreign key:

Model:

const Event = sequelize.define('event', {
  id: {
    type: DataTypes.UUID,
    primaryKey: true,
    allowNull: false,
    defaultValue: DataTypes.UUIDV4
  },
  createdBy: {
    type: DataTypes.UUID,
    allowNull: false,
    references: {
      model: 'user',
      key: 'id'
    },
    onUpdate: 'cascade',
    onDelete: 'restrict'
  },
  // ...
}

Migration:

return queryInterface.addColumn('event', 'createdBy', {
  type: Sequelize.UUID,
  allowNull: true,
  references: {
    model: 'user',
    key: 'id'
  },
  onUpdate: 'cascade',
  onDelete: 'restrict'
});

Result:

"createdBy_foreign_idx" FOREIGN KEY ("createdBy") REFERENCES "user"(id) ON UPDATE CASCADE ON DELETE RESTRICT
"event_createdBy_fkey" FOREIGN KEY ("createdBy") REFERENCES "user"(id) ON UPDATE CASCADE ON DELETE RESTRICT