Уникальный индекс по нескольким необязательным полям

Модераторы: kdv, CyberMax

Ответить
Yushinin
Сообщения: 19
Зарегистрирован: 10 июл 2005, 16:23

Уникальный индекс по нескольким необязательным полям

Сообщение Yushinin » 19 мар 2014, 15:04

Здравствуйте.
Столкнулся к не совсем понятным поведением Firebird при частичном заполнении необязательных полей, входящих в уникальный индекс.
Поясню примером.
Имеется такая таблица.

Код: Выделить всё

CREATE TABLE T (
    ID  INTEGER NOT NULL,
    A   INTEGER,
    B   INTEGER
);

ALTER TABLE T ADD CONSTRAINT PK_T PRIMARY KEY (ID);
ALTER TABLE T ADD CONSTRAINT UNQ1_T UNIQUE (A, B);
При попытке ввести несколько строк, которые имеют одинаковые значения A и null значения в B получаем исключение.

Код: Выделить всё


insert into t values(1,10,null);
insert into t values(2,10,null);

Invalid insert or update value(s): object columns are constrained - no 2 table rows can have duplicate column values.
violation of PRIMARY or UNIQUE KEY constraint "UNQ1_T" on table "T".

По троичной логике null отличается от другого null, поэтому уникальные индексы по одному необязательному полю работают исправно, а вот почему не принимаются одинаковые значения поля из составного индекса?

PS: Не сочтите за офтоп, но в порядке сравнения в MySQL все работает именно так, как я и ожидал.

Код: Выделить всё

CREATE TABLE IF NOT EXISTS `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNQ1_T` (`a`,`b`),
  KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

--
-- Дамп данных таблицы `t`
--

INSERT INTO `t` (`id`, `a`, `b`) VALUES
(1, 10, NULL),
(2, 10, NULL);

И только при попытке вставить определенно дубликаты, вызывает исключение.

Dimitry Sibiryakov
Заслуженный разработчик
Сообщения: 1436
Зарегистрирован: 15 сен 2005, 09:05

Re: Уникальный индекс по нескольким необязательным полям

Сообщение Dimitry Sibiryakov » 19 мар 2014, 15:34

Если поведение не совсем понятно, то перечитывай Release Notes 1.5 где эта фишка впервые появилась до просветления.

Yushinin
Сообщения: 19
Зарегистрирован: 10 июл 2005, 16:23

Re: Уникальный индекс по нескольким необязательным полям

Сообщение Yushinin » 19 мар 2014, 16:15

Ну прочитал.
Не могу согласится с фразой.
one (NULL, NULL) is distinct from any other (NULL, NULL)
Проверяем.

Код: Выделить всё

insert into t values (5,null, null);
insert into t values (6,null, null);
select distinct a,b from t;
Возвращается одна строка с null, null
Чем же они тогда отличаются?
То есть в уникальном индексе они distinct, а в запросе нет?

Прочитав внимательно стандарт, пришел к выводу, что реализовать так сказать "частично заполненный" уникальный индекс можно так:

Код: Выделить всё

CREATE OR ALTER TRIGGER T_BIU0 FOR T
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
as
begin
  if ((new.B is null) and
      (new.A is not null)) then
    new.B = new.ID;
end

Ответить