Oracle, UNIQUE KEYS and NULLs

как да накарам unique key и nulls да се държат адекватно? ;-)

create table test (id number null);
alter table TEST
add constraint SS unique (ID)
using index;
insert into test (null);
insert into test (null);
insert into test (null);

супер си бачка - вкарва всички редове


ОБАЧЕ!!!!

create table TEST2
(
ID NUMBER not null,
ID2 NUMBER
);
alter table TEST2
add constraint SS unique (ID,ID2)
using index;
insert into test2 (id) values (1);
insert into test2 (id) values (1); -- ГЪРМИ, ТА ПУШЕК СЕ ДИГА .... къде ги виде тез еднакви редове ........

имате ли идиа как да си направя contstraint-а, така че да ми дава

1, null
1, null
1, null
но не и
1, 1
1, 1

Проблема е в това, че Oracle не съхранява записи в индекса а само в таблицата, когато ти вмъкваш null стойности, затова и в първия случай можеш да insert-ваш колкото искаш празни записи и той ще си ги приема, щото в индекса нищо не влиза.
Във втория случай за него 1,null и 1,null са си еднакви записи, защото сравнява 1 с 1.
А как да се избегне, тук трябва да се помисли малко. Ако измисля нещо, ще пиша.

Мислих, но не се сещам за нещо подходящо , освен да попълваш и другата вместо с null с някакви автоматично генерирани уникални стойности, но не мисля, че е много подходящо. Според мен точно това е правилното поведение, защото null е особена стойност (или състояние) и не може да се третира по този начин. Не можеш да я включиш в логиката си заедно с другите данни, защото тя не е част от тях.
Но ако някой има интересно предложение, ще ми е интересно да го прочета.

ами според стандартите не е това правилното поведение .... защото null не е null то
1, null не е еднакво с 1, null

а не мога да му пляскам некви стойности, защото няма да има как да ги отлича от истинските си стойности, когато си има стойност ....

както и да е де, преструктурирах нещата по друг начин и заобиколих проблема, но все пак ако някой измисли нещо - нека свирне ;-)

ами понеже сега го виждам, мога само да ти гарантирам, че няма как да го постигнеш директно

т.е. null-а си е ok, но ако е само той (или всичките колони в multikey индекса са null) и е нормално, заради спецификата в булевата аритметика (дори се сещам как в JavaScript можеш да провериш стойност за Null като я сравниш със себе си - само null не е равно на себе си)

иначе за първия случай си е така - оракъл не индексира колони, където всичко е null, точно поради горната причина (не говорим за bitmap индекси)

да де аз си преобразувах таблиците доста и стана работата вече не разчитам на това

но точно защото ((null == null) == false) е true

1, null
и
1, null
не са еднакви!!

Може логиката на оракъла да е, че просто се сравняват не-null полетата от 2та записа. Все пак null е толкоз особена стойност, че си има проверка "is null" и по дефиниция (null == whatever) е винаги грешно.
Обаче не ми идва на акъла как да се дефинира ясно поведение в твоята ситуация, всъщност струва ми се най-правилно да се разрешава само или всичките полета от индекса да са null, или всичките да не са.
А конкретен workaround - неуникален индекс + тригер преди промяна/вмъкване, но сигурно вече си го направил това.

8 hobits = 1 hobyte

да с тригер щеше да стане, но има техническо задание от поръчителя да не се ползват тригери!! (
иначе да, тва е май най-доброто) иии просто преструктурирах таблиците

пробвай това

CREATE OR REPLACE FUNCTION f_uk(c1 NUMBER, c2 NUMBER) RETURN VARCHAR2
DETERMINISTIC AS
BEGIN
IF c2 IS NOT NULL
THEN
RETURN c1 || '-' || c2;
ELSE
RETURN NULL;
END IF;
END f_uk;

create table TEST2
(
ID NUMBER not null,
ID2 NUMBER
);

create unique index test_uk on TEST2(substr(f_uk(ID,ID2),1,100));


при мен работи(Oralce 9.2.0.1)

Успех!!

мда :-) тва е готино решение според мен

да, хитро решение

Похвално, щом човека е съгласен да използва unique index вместо constraint да - става.
При това положение, обаче в оригиналният пример, следният индекс би свършил същата работа.

create unique index TEST2_UK on TEST2 ( nvl2(ID2,ID,null),ID2 );

Защо трябва да е просто като може да е сложно!