VPF::В SQLite не работает UNIQUE для значений NULL

ZVano

Дата 12.5.2011, 16:12 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 258
Регистрация: 11.12.2006
Где: Украина, Кривой Р ог

Репутация: нет
Всего: 4

Делаю локальную БД для хранения натроек пользователя.
Необходимо наложить заклинание уникальности на комбинацию из двух полей.
При этом одно из полей может иметь значение 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)

У кого какие соображения по данной проблемме?
Как реализовать проверку уникальности на уровне БД?

Это сообщение отредактировал(а) ZVano - 12.5.2011, 16:34

--------------------

НЕ ФЛУДИМ. Пользуемся кнопками "+" или "-" для выражения своего отношения к теме или сообщению.
Гуглим "Как правильно задавать вопросы"

ZVano

Дата 13.5.2011, 14:42 (ссылка)

(голосов:1)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 258
Регистрация: 11.12.2006
Где: Украина, Кривой Р ог

Репутация: нет
Всего: 4

Народ, неужели никто не сталкивался с этой проблеммой?

Может, непонятно изложена суть проблеммы...
Тогда попробую привести наипростейший пример.
Тестовый скрипт

Код


--Создаем тестовую таблицу
drop table if exists peoples;
create table peoples(
    id INTEGER 
        PRIMARY KEY ASC AUTOINCREMENT
    , name1 TEXT
        COLLATE NOCASE
    , name2 TEXT
        COLLATE NOCASE
    , name3 TEXT
        COLLATE NOCASE
    , UNIQUE (name1 COLLATE NOCASE ASC, name2 COLLATE NOCASE ASC, name3 COLLATE NOCASE ASC)
);
insert into peoples(name1, name2, name3)values('1', '1', '1');
--insert into peoples(name1, name2, name3)values('1', '1', '1');-- При выполнении запроса произошла ошибка: columns name1, name2, name3 are not unique--

-- Следующие две строки не вызывают ошибки
insert into peoples(name1, name2, name3)values(null, '1', '1');
insert into peoples(name1, name2, name3)values(null, '1', '1');

-- Следующие две строки не вызывают ошибки
insert into peoples(name1, name2, name3)values('2', null, null);
insert into peoples(name1, name2, name3)values('2', null, null);

Вывод

Код


ID:name1:name2:name3:
1    1    1    1
1    1    1    1
2    null    1    1
3    null    1    1
4    2    null    null
5    2    null    null

--------------------

Gluttton

Дата 16.5.2011, 20:52 (ссылка)

(нет голосов)

Загрузка ...

Начинающий

Профиль
Группа: Завсегдатай
Сообщений: 1170
Регистрация: 28.8.2008
Где: Феодосия

Репутация: нет
Всего: 54

ZVano, никаких мыслей не возникает.
А все ли команды скрипта выполняются успешно? Что в логах?

--------------------

Слава Україні!

ZVano

Дата 17.5.2011, 10:03 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 258
Регистрация: 11.12.2006
Где: Украина, Кривой Р ог

Репутация: нет
Всего: 4

Цитата(Gluttton @ 16.5.2011, 20:52)

ZVano, никаких мыслей не возникает.
А все ли команды скрипта выполняются успешно? Что в логах?

Команды создания БД выполняются успешно.
В ответ получаю время выполнения скрипта.

Команда добавления дубликатов по полю "id_key" при "id_user" is NULL выполняется без ошибок.
В ответ получаю время выполнения скрипта.

Команда добавления дубликатов по полям "id_key" и "id_user" вызывает ошибку.
При выполнении запроса произошла ошибка: columns id_user, id_key are not unique

Вобщем то с причиной разобрался.
Подсказал "Basil A. Sidorov":: null ничему не равен. В том числе - другому null.
Соответственно, и не отрабатывает ни PRIMARY KEY, ни UNIQUE т.к. они не могут сравнить между собой два NULL-а.
В других СУБД (напр. Firebird) нельзя создать ни PRIMARY KEY, ни UNIQUE INDEX по полям, которые могут хранить NULL.
Т.е. в SQLite недоработка в том, что он вообще позволяет создавать такие ключи.
Ну, а выходов из ситуации два:
1. Пометить "id_user" как NOT NULL. И завести в справочнике пользователей псевдопользователя "default", на которого и ссылаться.
2. Не создавать уникальных индексов, а логику переложить на триггера BEFORE INSERT и BEFORE UPDATE.
Они обязаны проверить наличие дубликатов и откатить операцию, если таковые найдены.

Собираюсь использовать 2й подход. Правда, с синтаксисом DDL SQLite знаком весьма слабо.

Это сообщение отредактировал(а) ZVano - 17.5.2011, 10:04

--------------------

Gluttton

Дата 18.5.2011, 09:10 (ссылка)

(нет голосов)

Загрузка ...

Начинающий

Профиль
Группа: Завсегдатай
Сообщений: 1170
Регистрация: 28.8.2008
Где: Феодосия

Репутация: нет
Всего: 54