在mysql中,只有当值为false时,才有可能有一个唯一的约束吗?
例如,我想要对Name
的唯一约束,但前提是Archived = false
.
换句话说,我有一个字段archived
我将其用作软删除,如果我将唯一的 constraitn 放在 Name 周围并存档,删除两个同名的东西将导致违规。 因此想知道数据库是否可以处理这种类型的唯一约束
相关示例
我认为做到这一点的唯一方法是使用触发器和信号。 只需放置更新和插入的触发器即可。
类似方法的相关示例来自这里: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;
$$
最后,如果您只想要一个 NAME 用于 archived = false,但对于 archived = true 则无限制,您可能实际上对在 SQL 中使用以下语句感兴趣。
SELECT COUNT(id) FROM example_tbl WHERE name=NEW.name AND archived = 0;