Как в mysql сделать уникальный индекс при таком условии?

Question

[Сергей]

Есть таблица `my_table`. В ней есть поля id(INT), item_id(INT), is_main(0,1). Нужно чтобы соблюдалось условие, что у среди записей с одинаковым item_id может быть только одна запись is_main = 1, все остальные записи c одинаковым item_id должны быть is_main = 0. Собственно уникальный индекс по item_id+is_main не подходит, так как может быть много записей с is_main= 0 с одинаковым item_id. Как быть?

Answer 1

[evnuh]

Сделайте is_main NULLABLE и замените везде 0 на NULL. NULL-ы всегда уникальные, если можно так выразиться) Поэтому уникальный индекс по item_id + is_main будет ОК.

Answer 2

[Станислав Макаров]

Быть очень просто: выпиливаем поле is_main, т.к. оно вносит избыточность, за которой надо следить (собственно, ваше условие, что только одна запись может иметь is_main, равное 1). Вместо этого впиливаем таблицу main_data (назовите как считаете верным), такой структуры: (item_id, id), поле item_id - первичный ключ, плюс вся пара является внешним ключом на my_table. Итого, у вас две таблицы одинаковой структуры (id, item_id). Однако в первой (которая my_table) у вас ключ (id), т.к. может быть несколько таких записей на один item_id, а во второй (main_data) у вас ключ (item_id). Во вторую таблицу вы сможете вставить максимум один элемент для заданного item_id (может правда такой записи не быть вообще, это придется контроллировать в коде). Кроме того, за счет предложенного внешнего ключа вы не вставите в main_data элемент, которого нет в my_table.

Answer 3

[Rsa97]

Повесить триггеры BEFORE INSERT и BEFORE UPDATE и обнулять поле, если запись с 1 уже есть в базе. Ну или наоборот, обнулять 1 в базе, если вставляется/меняется запись с 1.

Answer 4

[Пума Тайланд]

Реализовывайте это на стороне приложения, либо меняйте логику в бд то есть хранить из мейн в отдельной таблице где есть значение айди итем и в нем одна цифра какой именно из элементов включён из мейн