列值等于false时的唯一约束？

Question

在mysql中，是否有可能只有当值为false时才有唯一的约束？

例如，我希望在Name上有一个唯一的约束，但前提是Archived = false。

换句话说，我有一个字段archived，我正在使用它作为软删除和删除两件具有相同名称的东西，如果我将唯一的约束放在名称周围并存档，就会导致违反。因此，想知道数据库是否能够处理这种类型的唯一约束。

Answer 1

相关实例

我认为唯一能做到这一点的方法就是使用触发器和信号。只需将触发器放入更新和插入即可。

类似方法的相关示例：http://cvuorinen.net/2013/05/validating-data-with-triggers-in-mysql/

DELIMITER $$

CREATE TRIGGER example_before_insert_allow_only_one_active
     BEFORE INSERT ON example_tbl FOR EACH ROW
     BEGIN
          IF NEW.active = 1 AND (SELECT COUNT(id) FROM example_tbl 
               WHERE active=1 AND foreign_key_id=NEW.foreign_key_id) > 0
          THEN
               SIGNAL SQLSTATE '45000'
                    SET MESSAGE_TEXT = 'Cannot add or update row: only one active row allowed per type';
          END IF;
     END;
$$

CREATE TRIGGER example_before_update_allow_only_one_active
     BEFORE UPDATE ON example_tbl  FOR EACH ROW
     BEGIN
          IF NEW.active = 1 AND (SELECT COUNT(id) FROM example_tbl
               WHERE id<>NEW.id AND active=1 AND foreign_key_id=NEW.foreign_key_id) > 0
          THEN
               SIGNAL SQLSTATE '45000'
                    SET MESSAGE_TEXT = 'Cannot add or update row: only one active row allowed per type';
          END IF;
     END;
$$

适用于你的案子

DELIMITER $$

CREATE TRIGGER example_before_insert_allow_only_one_not_archived
     BEFORE INSERT ON example_tbl FOR EACH ROW
     BEGIN
          IF NEW.archived = 0 AND (SELECT COUNT(id) FROM example_tbl 
               WHERE name=NEW.name) > 0
          THEN
               SIGNAL SQLSTATE '45000'
                    SET MESSAGE_TEXT = 'Cannot add or update row: existing name exists and you are entering with archived = 0 (false)';
          END IF;
     END;
$$

CREATE TRIGGER example_before_update_allow_only_one_not_archived
     BEFORE UPDATE ON example_tbl  FOR EACH ROW
     BEGIN
          IF  NEW.archived = 0 AND (SELECT COUNT(id) FROM example_tbl 
               WHERE name=NEW.name) > 0
          THEN
               SIGNAL SQLSTATE '45000'
                    S SET MESSAGE_TEXT = 'Cannot add or update row: existing name exists and you are entering with archived = 0 (false)';
          END IF;
     END;
$$

最后，如果您只想让存档= false的名称只有一个，而对存档= true没有限制，那么实际上您可能对在SQL中使用下面的语句感兴趣。

SELECT COUNT(id) FROM example_tbl  WHERE name=NEW.name AND archived = 0;