Создайте уникальное ограничение по индексу на основе функции с существующими повторяющимися значениями

У меня есть таблица с неправильными данными, и я хотел бы предотвратить вставку новых неправильных данных, пока я исправляю данные и выясняю, какой процесс или где это предложение делает это. Сначала я сделал ограничение UQ для столбцов, которые не должны дублироваться, но это приводит меня к другой проблеме: мне нужно применять уникальность только тогда, когда все столбцы имеют значение, если есть нули, мне нужно дублировать записи в этих столбцах. Что-то вроде этого:

CREATE TABLE MYTAB (COL1 NUMBER, COL2 NUMBER, COL3 NUMBER, COL4 NUMBER); --EXAMPLE TABLE. I NEED NO DUPS OVER (COL1, COL3, COL4)
INSERT INTO MYTAB VALUES (1, 1, 1, 1); --OK
INSERT INTO MYTAB VALUES (1, 2, 1, 1); -- NOOK
INSERT INTO MYTAB VALUES (1, 3, NULL, NULL); --OK
INSERT INTO MYTAB VALUES (1, 4, NULL, NULL); --OK

Если я создам такое ограничение:

ALTER TABLE MYTAB 
ADD CONSTRAINT U_CONSTRAINT UNIQUE (COL1, COL3, COL4)  NOVALIDATE;

Последняя вставка крашится.

я пробовал с

CREATE UNIQUE INDEX FN_UIX_MYTAB
    ON MYTAB (CASE WHEN COL2 IS NOT NULL THEN COL1 ELSE null END,
               CASE WHEN COL2 IS NOT NULL THEN COL3 ELSE null END
               CASE WHEN COL2 IS NOT NULL THEN COL4 ELSE null END) ;

Но при создании происходит сбой, потому что в таблице есть повторяющиеся данные. Мне нужно будет создать этот индекс без проверки существующих данных, что означает, что индекс будет применяться только к новым вставленным записям.

Я пробовал также с:

CREATE INDEX FN_IX_MYTAB
    ON MYTAB (CASE WHEN COL2 IS NOT NULL THEN COL1 ELSE null END,
               CASE WHEN COL2 IS NOT NULL THEN COL3 ELSE null END,
               CASE WHEN COL2 IS NOT NULL THEN COL4 ELSE null END) ;
ALTER TABLE MYTAB
ADD CONSTRAINT FN_UIX_MYTAB UNIQUE (COL1, COL3, COL4) USING INDEX FN_IX_MYTAB NOVALIDATE;

Но это дает мне ошибку:

ORA-14196: Specified index cannot be used to enforce the constraint.

Есть ли способ сделать то, что я объяснил, или я должен предотвратить неправильные вставки другим способом, пока я ищу источник проблемы? Любые советы также будут оценены.


duplicates oracle unique-constraint check-constraints deferred-execution
person EAmez    schedule 12.06.2017    source источник
comment
Если это временный обходной путь, пока вы не очистите свои таблицы, возможно, используйте триггер для предотвращения новых дубликатов, а затем удалите и добавьте правильное ограничение.   -  person OldProgrammer    schedule 12.06.2017
comment
Ваша формулировка проблемы не совсем логична. Вы хотите создать уникальный индекс для col1, col3, col4, но хотите разрешить дублирование (1, ..., NULL, NULL). Это не то, что означает UNIQUE в этих трех столбцах - проблема не в старых данных, нарушающих уникальность. Даже если вы сначала создадите таблицу, затем добавите это уникальное ограничение, а затем заполните таблицу в первый раз, вы не сможете добавить все строки в своем примере ТОЧНО по этой причине. Какое это имеет отношение к уже существующим данным?   -  person mathguy    schedule 12.06.2017
comment
@mathguy Я бы хотел, чтобы UNIQUE применялся только тогда, когда эти 3 столбца имеют значение. Это временный обходной путь, как указал OldProgrammer, он мне нужен только тогда, когда я нахожу источник проблемы и нахожу чистое решение.   -  person EAmez    schedule 13.06.2017

Ответы (1)


arrow_upward
0
arrow_downward

Вот один из возможных подходов. Создайте материализованное представление с обновлением при фиксации (предпочтительно быстрое обновление, если позволяют обстоятельства; в этом случае они должны). MV будет что-то вроде

create materialized view mymv
refresh fast on commit
as
select col1, col3, col4
from   mytab
where  col1 is not null and col3 is not null and col4 is not null
;

А затем установите уникальное ограничение (col1, col3, col4) на MV.

person mathguy    schedule 13.06.2017
comment
Но ограничение в представлении не предотвратит вставку неверных данных. Кроме того, я пробовал ваше решение, но оно не работает, потому что MyTab не имеет PK, потому что столбцы, которые делают каждую запись уникальной, — это столбцы, которые иногда должны быть пустыми. Да, я знаю, что это странный сценарий... но кто-то давно так делал :( - person EAmez; 13.06.2017
comment
Отсутствие ПК действительно является проблемой. Но в противном случае ограничение в MV предотвратит вставку неправильных данных. Это довольно распространенная стратегия для такого рода сценариев, я ее не придумал! Это работает так: вы пытаетесь вставить неправильные данные. При фиксации MV необходимо обновить. MV отклоняет новую строку, что, в свою очередь, делает недействительной всю транзакцию. - person mathguy; 13.06.2017