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

  • Looks like the type is empty. You talk about importing the data. Can you check the data for company 288837 on the 2017-05-15? This might help. – Marco Jun 16 '17 at 9:34
  • 1
    Seems it just works, but I had actually duplicate entries. Sequel Pro just didn't show me the value of the enum. After removing all duplicates, adding the the unique index seems to work correctly – Erik van de Ven Jun 16 '17 at 10:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.