如何仅在多个(超过 2 个)集合中强制仅将一组字段设为非 NULL?

问题描述

考虑这样的设置:

• 具有以下 TEXT 字段的multi"表:abc、def、ijk、lmn、uvw、xyz
• 将 s1 设为一组这些字段:abc、def
• 一组 s2 是一组这些字段:ijk, lmn
• 将 s3 设置为一组这些字段:uvw、xyz

我如何强制插入到multi"中的数据是这样的:

• 在一行中只能有一组是有效的.
• 如果该集合中的所有字段均非 NULL 且非空，则该集合被视为有效.
• 如果该集合中的任何字段为 NULL 或空，则该集合被视为无效.
• 一个有效的集合必须在所有行中都是唯一的.

这与 我之前提出的类似问题(已回答)有关，但想要看看是否有可能比下面的工作代码更优化(例如:更少的约束):

This is related to an earlier similar question from me (already answered), but wanted to see if a solution more optimal (eg: fewer constraints) than the working code below is possible:

DROP TABLE IF EXISTS multi;
CREATE TABLE multi(
    --set1
    abc TEXT,
    def TEXT,

    --set2
    ijk TEXT,
    lmn TEXT,

    --set3
    uvw TEXT,
    xyz TEXT,

    -- If some member in a set is NULL, all have to be NULL.
    CONSTRAINT set1_null CHECK((abc is NULL) = (def is NULL)),
    CONSTRAINT set2_null CHECK((ijk is NULL) = (lmn is NULL)),
    CONSTRAINT set3_null CHECK((uvw is NULL) = (xyz is NULL)),

    -- If some member in a set in non-NULL, all have to be non-NULL
    CONSTRAINT set1_ntnl CHECK((abc is NOT NULL) = (def is NOT NULL)),
    CONSTRAINT set2_ntnl CHECK((ijk is NOT NULL) = (lmn is NOT NULL)),
    CONSTRAINT set3_ntnl CHECK((uvw is NOT NULL) = (xyz is NOT NULL)),

    -- A set cannot have members of empty strings.
    CONSTRAINT set1_ntmt CHECK((abc is NOT "") AND (def is NOT "")),
    CONSTRAINT set2_ntmt CHECK((ijk is NOT "") AND (lmn is NOT "")),
    CONSTRAINT set3_ntmt CHECK((uvw is NOT "") AND (xyz is NOT "")),

    -- If all members in a set are non-NULL, all others sets should only have NULL members.
    CONSTRAINT set1_excl CHECK((COALESCE(abc, def) is NOT NULL) = ((COALESCE(ijk, lmn) is NULL) AND COALESCE(uvw, xyz) is NULL))
    CONSTRAINT set1_excl CHECK((COALESCE(ijk, lmn) is NOT NULL) = ((COALESCE(uvw, xyz) is NULL) AND COALESCE(abc, def) is NULL))
    CONSTRAINT set1_excl CHECK((COALESCE(uvw, xyz) is NOT NULL) = ((COALESCE(abc, def) is NULL) AND COALESCE(ijk, lmn) is NULL))

    -- A set can have only unique combination of its non-NULL members.
    CONSTRAINT set1_uniq UNIQUE(abc, def),
    CONSTRAINT set2_uniq UNIQUE(lmn, ijk),
    CONSTRAINT set3_uniq UNIQUE(uvw, xyz)
);

.echo on
INSERT INTO multi(abc, def)  VALUES("a1", "d1");    -- should pass: unique set1
INSERT INTO multi(abc, def)  VALUES("a1", "d1");    -- should FAIL: duplicate set1

INSERT INTO multi(ijk, lmn)  VALUES("i3", "l3");    -- should pass: unique set2
INSERT INTO multi(ijk, lmn)  VALUES("i3", "l3");    -- should FAIL: duplicate set2

INSERT INTO multi(uvw, xyz)  VALUES("u5", "x5");    -- should pass: unique set3
INSERT INTO multi(uvw, xyz)  VALUES("u5", "x5");    -- should FAIL: duplicate set3

INSERT INTO multi(abc, def)  VALUES(NULL, NULL);    -- should FAIL: null set1
INSERT INTO multi(ijk, lmn)  VALUES(NULL, NULL);    -- should FAIL: null set2
INSERT INTO multi(uvw, xyz)  VALUES(NULL, NULL);    -- should FAIL: null set3

INSERT INTO multi(abc, def)  VALUES("", "");        -- should FAIL: empty set1
INSERT INTO multi(ijk, lmn)  VALUES("", "");        -- should FAIL: empty set2
INSERT INTO multi(uvw, xyz)  VALUES("", "");        -- should FAIL: empty set3

INSERT INTO multi(abc)       VALUES(NULL);          -- should FAIL: incomplete set1
INSERT INTO multi(abc)       VALUES("");            -- should FAIL: incomplete set1
INSERT INTO multi(abc)       VALUES("a15");         -- should FAIL: incomplete set1
INSERT INTO multi(abc, ijk)  VALUES("a16", "i16");  -- should FAIL: incomplete set1

INSERT into multi(abc, def, ijk, lmn, uvw, xyz)  VALUES("", "", "", "", "", "");  -- should FAIL:
INSERT into multi(abc, def, ijk, lmn, uvw, xyz)  VALUES(null, null, null, null, null, null);  -- should FAIL:
INSERT into multi(abc, def, ijk, lmn, uvw, xyz)  VALUES("a19", "b19", "", "", null, null); -- should FAIL:
-- etc

-- ------------------------------------
-- Only these 3 rows should be present:
-- ##|abc|def|ijk|lmn|uvw|xyz
-- 1 |a1 |d1 |   |   |   |
-- 2 |   |   |i3 |l3 |   |
-- 3 |   |   |   |   |u5 |x5
-- ------------------------------------
.headers ON
select rowid AS ROW, * from multi;

推荐答案

您不需要 setX_ntnl 约束；IS NULL 和 IS NOT NULL 总是返回相反的结果.

You do not need the setX_ntnl constraints; IS NULL and IS NOT NULL always return opposite results.

setX_excl 约束不需要使用 COALESCE，因为早期的 setX_null 约束已经强制集合中的所有列都具有相同类型的值；您可以简单地比较每组中的一列.还有一种更简单的方法来检查三个集合中的一个是否有效:有效集合的数量必须是一个:

The setX_excl constraints do not need to use COALESCE because the earlier setX_null constraints already enforce that all columns in a set have the same kind of value; you could simply compare one column in each set.And there is an easier method to check that exactly one of the three sets is valid: the number of valid sets must be one:

CONSTRAINT set123_excl CHECK((abc IS NOT NULL) +
                             (ijk IS NOT NULL) +
                             (uvw IS NOT NULL) = 1)

(在 SQLite 中，布尔表达式返回 0 或 1.)

(In SQLite, boolean expressions return 0 or 1.)

这篇关于如何仅在多个(超过 2 个)集合中强制仅将一组字段设为非 NULL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！