Как принудительно применять только один набор полей для не-NULL исключительно среди множества (более 2) наборов?

Рассмотрим настройку, такую ​​как:

  • Таблица «multi», чтобы иметь эти поля TEXT: abc, def, ijk, lmn, uvw, xyz
  • Множество s1 является группой этих полей: abc, def
  • Множество s2 является группой этих полей: ijk, lmn
  • Множество s3 должно быть группой этих полей: uvw, xyz

Как включить данные, вставленные в «multi», таковы, что:

  • Только один из наборов может быть действительным в строке.
  • Набор считается действительным, если все поля в этом наборе не являются NULL и непустыми.
  • Набор считается недопустимым, если любое поле в этом наборе равно NULL или пусто.
  • Действительный набор должен быть уникальным для всех строк.

Это связано с ранее подобным вопросом от меня (уже ответил), но хотелось бы посмотреть, возможно ли решение более оптимальным (например, меньше ограничений), чем рабочий код ниже:

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 и NOT NULL всегда возвращают противоположные результаты.

Ограничения setX_excl не должны использовать COALESCE, потому что более setX_null ограничения setX_null уже обеспечивают, чтобы все столбцы в наборе имели одинаковый вид; вы можете просто сравнить один столбец в каждом наборе. И есть более простой способ проверить, что верно только один из трех наборов: количество допустимых наборов должно быть одним:

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

(В SQLite булевские выражения возвращают 0 или 1 )

Я не использую SQLite, но я читал, что используя || для конкатенации строк будет выдаваться нулевой результат, если одна строка равна NULL. Это полезно, потому что мы можем гарантировать, что обе строки не являются нулевыми, объединяя их. a || b a || b должно означать, что оба значения не равны нулю, в противном случае – нулевые. Я также думаю, что SQLite обрабатывает пустую строку и null как другую, то есть мы должны проверить, что a || b a || b не совпадает с a или b ..

Тогда базовая проверка может быть COALESCE(a || b, '') NOT IN (a, b, '') :

  • Если a или b (или оба) являются нулевыми, нулевые результаты, COALESCE преобразует его в пустую строку, пустая строка является одной из установленных результатов, а NOT IN
  • Если a и b являются пустой строкой, пустая строка получается из коалесценции и снова является членом «недопустимого» набора
  • Если a является значением и b пуст, результатом является (и это член «не разрешен»). Аналогичная логика следует для b, имеющего значение, и пустое

Логика доказывает только один набор. Если строка должна иметь только один из трех заданных наборов, то она должна быть «доказать только первый набор» ИЛИ «доказать второй набор в одиночку» ИЛИ «доказать третий набор в одиночку»,

Чтобы доказать, что набор является единственным в строке, после проверки его правильности с вышеприведенными тестами, мы должны проверить другие наборы, что их значения – все только пустые строки. Самый простой способ сделать это – объединить их все и убедиться, что результатом является только пустая строка. Если какое-либо значение больше 0, результат не является пустой строкой. Если любое значение равно null, результат не является пустой строкой

Вот полное (и единственное) ограничение:

 CONSTRAINT a CHECK ( (COALESCE(abc || def, '') NOT IN (abc, def, '') AND ijk||lmn||uvw||xyz='') OR (COALESCE(ijk || lmn, '') NOT IN (ijk, lmn, '') AND abc||def||uvw||xyz='') OR (COALESCE(uvw || xyz, '') NOT IN (uvw, xyz, '') AND abc||def||ijk||lmn='') ) 

Создание первичного ключа abc, def, ijk, lmn, uvw, xyz должно быть единственным, что необходимо для реализации функции уникальности, если только набор (abc = 'a', def = 'b') не является то же самое, что (abc = 'b', def = 'a')

Будьте осторожны, что это не «краткость за счет ясности»,