Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Oracle > null values unique key

Автор: Samotnik 6.5.2011, 11:58

Привет

Подскажите плиз решение сложившейся ситуации.
Есть таблица в БД, в которой есть композитный ключ, который состоит из поля name и boolean поля deleted.
Возникла проблема, при попытке сделать следующее:
INSERT INTO User (name, deleted) VALUES ('dima', false); -- OK
INSERT INTO User (name, deleted) VALUES ('dima', null); -- OK
INSERT INTO User (name, deleted) VALUES ('dima', null); -- Exception !!! java.sql.SQLException: ORA-00001: unique constraint violated
В MySQL, такое работает, т.е. можно сколько угодно создать записей ('dima', null), в Oracle нет.
Нашёл похожую проблему в тырнете, предлагают ее решить вот так:

Код


CREATE UNIQUE INDEX table_1_un ON table_1
(CASE WHEN (col_1 IS NOT NULL AND col_2 IS NOT NULL) THEN col_1 ELSE NULL END,
CASE WHEN (col_2 IS NOT NULL AND col_2 IS NOT NULL) THEN col_2 ELSE NULL END);

Только я не понимаю, как это применить к моей ситуации, подскажите пожалуйста
smile

Автор: Zloxa 6.5.2011, 12:47

Цитата(Samotnik @ 6.5.2011, 11:58

)

false

в SQL оракла же нет такого слова. smile

Цитата(Samotnik @ 6.5.2011, 11:58

)

решение сложившейся ситуации.

если тебе надо обеспечить уникальность не удаленных записей...

Код


create unique index mytable$name$unc unique(decode(deleted,'N',name));

Оракл не хранит в индексе неопределенных значений. Функция, по которой построен индекс, возвращает не определенное значение, если срока помечена как удаленная.
В 11й датабазе появились вычислимые столбцы. Мне кажется использовать вычислимый столбец тут было бы более кошерно.

Автор: Samotnik 6.5.2011, 12:54

Цитата(Zloxa @ 6.5.2011, 12:47

)

в оракле же нет такого слова.

тут длинная история, я пишу на Java, коннект к БД через hibernate, у хибера подключен ораклавский диалект. В итоге я пишу код на Java false, а что там именно сетится в оракл, я не знаю smile

Попробовал твой код

Код


create unique index myuser_unc unique(decode(deleted,'N',name));

ошибка: ora-00969

Добавлено через 1 минуту и 58 секунд

Цитата(Zloxa @ 6.5.2011, 12:47

)

если тебе надо обеспечить уникальность не удаленных записей...

мне нужно обеспечить чтобы у меня в таблице было только одно ('dima', 0) и сколько угодно ('dima', null)
smile

Автор: Zloxa 6.5.2011, 12:56

Цитата(Samotnik @ 6.5.2011, 12:54

)

ora-00969

ORA-00969: missing ON keyword
вставить после имени индекса:

Код


 on <имя таблицы>

Добавлено через 42 секунды

Код


create unique index myuser_unc on mytable (decode(deleted,'N',name));

Автор: Samotnik 6.5.2011, 12:58

http://stackoverflow.com/questions/1374737/how-can-i-create-a-unique-index-in-oracle-but-ignore-nulls похожая проблема, но почему то у меня не работает это решение, я пишу

Код


create unique index myuser_uidx on myuser
(nvl2(deleted, name, null), nvl2(name, deleted, null))

индекс создается, но всё равно, при попытке вставить второй ('dima', null) - получаю ORA-00001: unique constraint violated

Добавлено через 4 минуты и 23 секунды
Zloxa, спасибо, выполнил твой

Код


create unique index myuser_unc on myuser (decode(deleted,'N',name));

всё равно, при попытке вставить второго ('dima', null) - ORA-00001: unique constraint violated

Автор: Zloxa 6.5.2011, 13:10

Цитата(Samotnik @ 6.5.2011, 12:58

)

я пишу

Код


create unique index myuser_uidx on myuser
(nvl2(deleted, name, null), nvl2(name, deleted, null)

здесь решается совсем другая задача. Данный индекс допустит повторение name если не заполнен deleted и повторение deleted лишь если не заполнен name.

Цитата(Samotnik @ 6.5.2011, 12:58

)

но всё равно, при попытке вставить второй ('dima', null) - получаю ORA-00001: unique constraint violated

Надо разбраться во что хибер преобразует false. В этом ключ к успеху действа

Добавлено @ 13:15

Цитата(Samotnik @ 6.5.2011, 12:58

)

('dima', null)

если false преобразуется в null, то индексирваться должно выражение (decode(deleted,null,name))

Автор: Samotnik 6.5.2011, 13:16

Цитата(Zloxa @ 6.5.2011, 13:10

)

Надо разбраться во что хибер преобразует false. В этом ключ к успеху действа

c false проблемы нету, есть проблема с null. А null остается null'ом без преобразований

Добавлено через 1 минуту и 53 секунды

Цитата(Zloxa @ 6.5.2011, 13:10

)

здесь решается совсем другая задача. Данный индекс допустит повторение name если не заполнен deleted

так ведь мне это и нужно, мне нужно разрешить повторение name, если deleted == null

Добавлено через 2 минуты и 24 секунды
логично ли предположить, что в таком случае

Код


create unique index myuser_uidx on myuser
(nvl2(deleted, name, null)

спасет меня ?

Автор: Zloxa 6.5.2011, 13:25

Код


SQL> create table test (name varchar2(10),deleted number);
 
Table created
SQL> create unique index test$idx on test (nvl2(deleted,name,null),nvl2(name,deleted,null));
 
Index created
SQL> insert into test values ('дима',1);
 
1 row inserted
SQL> insert into test values ('дима',null);
 
1 row inserted
SQL> insert into test values ('дима',null);
 
1 row inserted
SQL> insert into test values (null,1);
 
1 row inserted
SQL> insert into test values (null,1);
 
1 row inserted
SQL> insert into test values ('дима',1);
 
insert into test values ('дима',1)
 
ORA-00001: unique constraint (ODYSSEY_GATE.TEST$IDX) violated

тебе так надо?

Автор: Samotnik 6.5.2011, 13:29

Цитата(Zloxa @ 6.5.2011, 13:25

)

тебе так надо?

почти, нужно вот так:
insert into test values ('дима',0); - ok
insert into test values ('дима',null); - ok
insert into test values ('дима',null); - ok
insert into test values ('дима',null); - ok
insert into test values ('дима',null); - ok
insert into test values ('дима',null); - ok
insert into test values ('дима',null); - ok
insert into test values ('дима',0); - тут должна быть ошибка, потому что уже есть "дима" не удаленный, у которого deleted - 0 (т.е. false)

Автор: Zloxa 6.5.2011, 13:43

Цитата(Samotnik @ 6.5.2011, 13:29

)

deleted - 0 (т.е. false)

я ж говорил - ключ - в этом ))

Код


SQL> create table test (name varchar2(10),deleted number);
 
Table created
SQL> create unique index test$idx on test (decode(deleted,0,name));
 
Index created
SQL> insert into test values ('дима',0);
 
1 row inserted
SQL> insert into test values ('дима',null);
 
1 row inserted
SQL> insert into test values ('дима',null);
 
1 row inserted
SQL> insert into test values ('дима',1);
 
1 row inserted
SQL> insert into test values ('дима',1);
 
1 row inserted
SQL> insert into test values ('дима',0);
 
insert into test values ('дима',0)
 
ORA-00001: unique constraint (ODYSSEY_GATE.TEST$IDX) violated

Добавлено через 9 минут и 28 секунд

Цитата(Samotnik @ 6.5.2011, 12:54

)

мне нужно обеспечить чтобы у меня в таблице было только одно ('dima', 0) и сколько угодно ('dima', null)

жаль не заметил раньше smile

Автор: Samotnik 6.5.2011, 14:27

Zloxa, спасибо, да, действительно всё работает, но.
Если у меня уже есть индекс, под названием SYS_C009835, который мешает работе нового, можно как-нибудь приглушить его ? smile

Автор: Zloxa 6.5.2011, 15:01

если он тебе не нужен - дропни его. ))

Автор: Samotnik 6.5.2011, 15:06

Zloxa, в общем я понял, спасибо ! smile