Делаю локальную БД для хранения натроек пользователя.
Необходимо наложить заклинание уникальности на комбинацию из двух полей.
При этом одно из полей может иметь значение NULL.
Краткое описание:
| Код |
/*Создаем тестовую таблицу, в которой нужно реализовать проверку уникальности по паре полей id_user, id_key
*/
CREATE TABLE named_values(
id_user TEXT
REFERENCES users(login) ON UPDATE CASCADE
, id_key TEXT
NOT NULL
COLLATE NOCASE
REFERENCES named_values_list (key_name) --ON DELETE CASCADE
, key_value TEXT
-- Определяем первичный ключ, который ОБЯЗАН быть уникальным, но на самом деле обламывается в моем случае
, PRIMARY KEY (id_user, id_key)
-- Ограничение уникальности на два поля тоже не дает нужного результата
--, UNIQUE (id_user COLLATE NOCASE ASC, id_key COLLATE NOCASE ASC)
);
/*Заполняем тестовыми данными.
Если id_key = NULL, то ограничение не срабатывает.
Если не NULL, то ограничение работает корректно.
*/
insert into named_values(id_user, id_key, key_value)values(null, 'tEsT', 'test1');
--Следующие две строки НЕ вызовут ошибку ограничения уникальности, хотя и ОБЯЗАНЫ
insert into named_values(id_user, id_key, key_value)values(null, 'test', 'test2.1');
insert into named_values(id_user, id_key, key_value)values(null, 'test', 'test2.2');
|
Скрипт создания БД.
| Код |
-- Прибиваем БД.
drop table if exists named_values;
drop table if exists named_values_list;
drop table if exists users;
drop table if exists languages;
--break;
/*Справочник языков.
lang - ключ языка
caption - отображаемое значение.
*/
CREATE TABLE languages (
lang TEXT(3)
PRIMARY KEY
--NOT NULL UNIQUE
, caption TEXT
NOT NULL
,is_deleted BOOLEAN
NOT NULL
DEFAULT(0)
CHECK (is_deleted in (0, 1))
);
/*Справочник пользователей системы.
login - логин пользователя (под которым пользователь заходил в основную систему).
*/
CREATE TABLE users (
login TEXT
NOT NULL UNIQUE
, comment TEXT
,is_deleted BOOLEAN
NOT NULL
DEFAULT(0)
CHECK (is_deleted in (0, 1))
);
/*key_val_types
val_type Наименование типа для значения
*/
CREATE TABLE named_values_list(
key_name TEXT PRIMARY KEY
COLLATE 'NOCASE'
, comment TEXT
, is_deleted BOOLEAN
NOT NULL
DEFAULT (0)
CHECK (is_deleted in (0, 1))
);
/*Именованые данные пользователя.
Ключ=значение в разрезе пользователей.
Если пользователь не указан, значит значение действует для всех пользователей, у которых нет таекого значения.
*/
CREATE TABLE named_values(
id_user TEXT
REFERENCES users(login) ON UPDATE CASCADE
, id_key TEXT
NOT NULL
COLLATE NOCASE
REFERENCES named_values_list (key_name) --ON DELETE CASCADE
, key_value TEXT
-- Определяем первичный ключ, который ОБЯЗАН быть уникальным, но на самом деле обламывается в моем случае
, PRIMARY KEY (id_user, id_key)
-- Ограничение уникальности на два поля тоже не дает нужного результата
--, UNIQUE (id_user COLLATE NOCASE ASC, id_key COLLATE NOCASE ASC)
);
|
Скрипт заполнения созданой БД тестовыми значениями.
| Код |
--Чистим таблицы БД
delete from named_values;
delete from named_values_list;
delete from users;
delete from languages;
insert into languages (lang, caption)values('RUS', 'Русский');
insert into languages (lang, caption)values('UKR', 'Українська');
insert into languages (lang, caption)values('ENG', 'Englesh');
insert into users(login) values('ZVano');
insert into users(login) values('User1');
insert into users(login) values('User2');
-- Заполняем таблицы тестовыми данными
insert into named_values_list(key_name, comment)values('test', 'Тестовый параметр');
insert into named_values_list(key_name, comment)values('dbname', 'Название БД для подключения');
insert into named_values_list(key_name, comment)values('app-theme', 'Приложение\Тема - выбраная пользователем тема');
insert into named_values(id_user, id_key, key_value)values('ZVano', 'dbname', 'localhost:3329');
insert into named_values(id_user, id_key, key_value)values('ZVano', 'app-theme', 'Тема для ZVano');
insert into named_values(id_user, id_key, key_value)values(null, 'app-theme', 'default');
-- Тесты
insert into named_values(id_user, id_key, key_value)values('ZVano', 'test', 'test3');
--Следующая строка вызовет ошибку ограничения уникальности, поэтому закомментирована
--insert into named_values(id_user, id_key, key_value)values('ZVano', 'tEsT', 'test4');
insert into named_values(id_user, id_key, key_value)values(null, 'tEsT', 'test1');
--Следующие две строки НЕ вызовут ошибку ограничения уникальности, хотя и ОБЯЗАНЫ
insert into named_values(id_user, id_key, key_value)values(null, 'test', 'test2.1');
insert into named_values(id_user, id_key, key_value)values(null, 'test', 'test2.2');
|
В результате таблица named_values будет содержать такие данные:
| Код |
ROWID |id_user |id_key |key_value
-------|---------|---------|-----------------
1|ZVano |dbname |localhost:3329
2|ZVano |app-theme|Тема для ZVano
3|(null) |app-theme|default
4|ZVano |test |test3
5|(null) |test |test1.1
6|(null) |test |test1.2
7|(null) |tEsT |test2
|
В строках 5, 6 явное нарушение PRIMARY KEY, а в строке 7 конфликт по COLLATE NOCASE (для заккоментированой строчки UNIQUE)
У кого какие соображения по данной проблемме?
Как реализовать проверку уникальности на уровне БД? |
)
.