Как создать "уникальное" ограничение для булевского столбца Mysql?

Я хотел бы добавить столбец BOOLEAN в таблицу MySQL, которая будет называться is_default. В этом столбце только одна запись может иметь is_default для true.

Как добавить это ограничение в мой столбец с помощью mysql?

Спасибо!

ОБНОВЛЕНИЕ

Если это не ограничение, которое я должен добавить. Как мы имеем дело с этим типом проблемы в БД?

Ответ 1

Я думаю, что это не лучший способ моделировать ситуацию с единственным значением по умолчанию.

Вместо этого я оставил бы столбец IsDefault и создаю отдельную таблицу с одной строкой и только столбец (столбцы), которые делают первичный ключ вашей основной таблицы. В этой таблице вы помещаете значения PK, которые идентифицируют запись по умолчанию.

Это приводит к значительно меньшему хранению и позволяет избежать проблемы с обновлением, временно не имеющей значения по умолчанию (или, альтернативно, временно имеющие два значения по умолчанию) при обновлении.

У вас есть множество опций для обеспечения наличия одной и той же строки в таблице по умолчанию.

Ответ 2

У вас не может быть такого ограничения в MySQL.

Однако если вместо TRUE и FALSE вы используете значения TRUE и NULL, то это будет работать, потому что столбец UNIQUE может иметь несколько значений NULL. Обратите внимание, что это не относится ко всем базам данных, но оно будет работать в MySQL.

CREATE TABLE table1(b BOOLEAN UNIQUE);

INSERT INTO table1 (b) VALUES (TRUE);   // Succeeds
INSERT INTO table1 (b) VALUES (TRUE);   // Fails: duplicate entry '1' for key 'b'

INSERT INTO table1 (b) VALUES (FALSE);  // Succeeds
INSERT INTO table1 (b) VALUES (FALSE);  // Fails: duplicate entry '0' for key 'b'

INSERT INTO table1 (b) VALUES (NULL);   // Succeeds
INSERT INTO table1 (b) VALUES (NULL);   // Succeeds!

Ответ 3

Как мы имеем дело с этим типом проблемы в БД?

В некоторых СУБД вы можете создать частичный индекс.

В PostgreSQL это будет выглядеть так:

CREATE UNIQUE INDEX only_one_true 
  ON the_table (is_default)
  WHERE is_default

SQL Server 2008 имеет очень похожий синтаксис.

В Oracle это немного сложнее, но выполнимо:

CREATE UNIQUE INDEX only_one_true 
  ON the_table (CASE 
                  WHEN is_default = 1 THEN 1
                  ELSE null
                END)

Решение Oracle может работать на любой СУБД, поддерживающей выражение для определения индекса.

Ответ 4

Проверьте триггеры. Полагаю, они были представлены в версии 5.0.2. Вам нужен триггер "перед вставкой". Если уже есть строка с is_default = true, вызовите ошибку. Я не знаю, какие проблемы вы могли бы с помощью concurrency и так далее, но, надеюсь, этого вам достаточно, чтобы вы начали.

Ответ 5

Я не думаю, что это проблема с базой данных, так как это проблема с вашей моделью. Мне трудно придумать хороший пример того, как его решить, поскольку вы не указали, какой тип данных вы представляете, но таблица XXXType или XXXConfiguration может содержать defaultXXXId колонка.

Подумайте об этом так: Если синий цвет знает, что он по умолчанию или должен что-то еще знать, что цвет синий по умолчанию используется в данном контексте?

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

Ответ 6

Проверить ограничения не поддерживаются в MySQL, это решение с помощью триггера:

    create table if not exists myTable (
        id int not null auto_increment primary key,
        is_default bit not null
    ) engine=innodb;

    select 'create trigger tbi_myTable';
    drop trigger if exists tbi_myTable;
    delimiter //
    create trigger tbi_myTable 
        before insert on myTable 
        for each row
    begin
        if (select count(1) from myTable where is_default=true) > 0 && NEW.is_default then
        -- Signal is only in 5.6 and above use another way to raise an error: if less than 5.6
            SIGNAL SQLSTATE '50000' SET MESSAGE_TEXT = 'Cannot insert into myTable only one row with is_default true is allowed!';
        end if;
    END //
    delimiter ;

    insert into myTable (is_default) values (false);
    insert into myTable (is_default) values (true);
    insert into myTable (is_default) values (false);
    insert into myTable (is_default) values (false);
    -- This will generate an error
    insert into myTable (is_default) values (true);
    insert into myTable (is_default) values (false);


    select * from myTable;
    -- will give
    /*
    id  is_default
    1   false
    2   true
    3   false
    4   false
    */