Mysql: enum part of unique Index

by Erik van de Ven   Last Updated June 16, 2017 10:06 AM

Is it possible to have an unique index on multiple columns, of which a column with type enum is part of?

Let's say I have 3 columns, which are an unique index, combined:

  • date
  • type
  • companyId

date is just a date, companyId is an integer, but type is an enum, which could be week, day or month. Reason to have this, is because the table contains imported statistics. A company has only one row per date, per type. The index is to prevent data will be imported twice, accidentally.

I tried the following which did not work:

ALTER TABLE `stats` ADD UNIQUE INDEX `unique_index` (`companyId`, `date`, `type`);

The error I received was:

MySQL said: Duplicate entry '288837-2017-05-15-' for key 'unique_index'

As you can see the key doesn't contain the type, only the companyId and date

Related Questions

Dictionary table vs varchar vs enum?

Updated November 21, 2017 20:06 PM

How does MySQL handle joins on ENUMs?

Updated May 22, 2018 20:06 PM

How to change a column type from SET to an ENUM

Updated December 04, 2016 08:02 AM