CODEWITHSUNDEEP
sqlsqlite
Sathya Meda
Sathya Meda

Reputation: 35

How to enforce multiple unique sets (column-combinations) to be mutually-exclusive in SQLite?

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

  1. Set 1 ('abc') to be UNIQUE if 'abc' is not null, and
  2. Set 2 ('def, xyz') to be UNIQUE if both 'def' and 'xyz' are not null, and
  3. Only one of the above sets is valid (contain non-nulls).

Here is the code I have tried:

--# 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
--

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

Upvotes: 1

Views: 199

Answers (1)

CL.
CL.

Reputation: 180290

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))

Upvotes: 1

Related Questions