如何在 SQLite 中强制多个唯一集(列组合)互斥?

问题描述

考虑一个包含可以为空的列 abc、def 和 xyz 的表.我如何强制执行:

Consider a table containing null-able columns abc, def and xyz. How do I enforce:

1. 如果 'abc' 不为空，则将 1 ('abc') 设置为 UNIQUE，并且
2. 如果 'def' 和 'xyz' 都不为空，则将 2 ('def, xyz') 设置为 UNIQUE，并且
3. 上述集合中只有一个是有效的(包含非空值).

这是我试过的代码:

--# sqlite3 --version
--  3.13.0 ....

DROP TABLE IF EXISTS try;
CREATE TABLE try(
    -- 'abc' should be null,                  when 'def' and 'xyz' are not null.
    -- 'abc' should be not be null,           when 'def' and 'xyz' are null.
    -- 'def' and 'xyz' should be null,        when 'abc' is not null.
    -- 'def' and 'xyz' should be not be null, when 'abc' is null.

    abc  TEXT,
    def  TEXT,
    xyz  TEXT,
    CONSTRAINT combo_1 UNIQUE(abc),
    CONSTRAINT combo_2 UNIQUE(def, xyz)
);

INSERT into try(abc)            VALUES("a1");              -- should succeed
INSERT into try(def, xyz)       VALUES("d2", "x2");        -- should succeed
--
INSERT into try(abc)            VALUES(null);              -- should not be allowed
INSERT into try(abc, def)       VALUES("a4", "d4");        -- should not be allowed
INSERT into try(abc, xyz)       VALUES("a5", "x5");        -- should not be allowed
INSERT into try(abc, def, xyz)  VALUES("a6", "d6", "x6");  -- should not be allowed
--
INSERT into try(def)            VALUES(null);              -- should not be allowed
INSERT into try(def)            VALUES("d8");              -- should not be allowed
--
INSERT into try(xyz)            VALUES(null);              -- should not be allowed
INSERT into try(xyz)            VALUES("x10");             -- should not be allowed
--
INSERT into try(def, xyz)       VALUES(null, null);        -- should not be allowed
INSERT into try(def, xyz)       VALUES("d12", null);       -- should not be allowed
INSERT into try(def, xyz)       VALUES(null, "x13");       -- should not be allowed

INSERT into try(abc, def, xyz)  VALUES(null, null, null);  -- should not be allowed

.headers ON
select rowid,* from try;

.echo on
--
-- Only these 2 rows should be present:
-- 1|a1||
-- 2||d2|x2
--

但是，当我只希望前 2 个成功时，所有 14 个插入都成功了.

But, all the 14 inserts are succeeding, when I wanted only the first 2 to succeed.

推荐答案

换句话说:abc 和 def 列的 NULL-ity 应该不同，而def 和 xyz 列应该相同.

In other words: the NULL-ity of the abc and def columns should be different, while that of the def and xyz columns should be the same.

这可以通过两个额外的表约束来完成:

This can be done with two additional table constraints:

CHECK((abc IS NULL) <> (def IS NULL)),
CHECK((def IS NULL) =  (xyz IS NULL))

这篇关于如何在 SQLite 中强制多个唯一集(列组合)互斥?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！