How to make a unique index in mysql under such a condition?

Question

MySQL

There is a table `my_table`. It has fields id(INT), item_id(INT), is_main(0,1). It is necessary that the condition be observed that among records with the same item_id there can be only one record is_main = 1, all other records with the same item_id must be is_main = 0. A unique index by item_id + is_main is not suitable, since there can be many records with is_main= 0 with the same item_id. How to be?

Answer 1

Make is_main NULLABLE and replace 0 with NULL everywhere. NULLs are always unique, so to speak) Therefore, a unique index on item_id + is_main will be OK.

Answer 2

Implement this on the application side, or change the logic in the database, that is, store from main in a separate table where there is an item id value and it has one digit which of the elements is included from main