oracle 使用现有重复值在基于函数的索引上创建唯一约束

u4vypkhs 于 2023-10-16 发布在 Oracle

关注(0)|答案(2)|浏览(164)

我有一个错误的数据表，我想防止新的错误数据被插入，而我修复数据，并找出是什么过程或在哪里的句子，使这发生。我首先对不应该被复制的列做了一个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.

有没有一种方法可以做到我所解释的，或者我应该以另一种方式防止错误的插入，而我寻找问题的根源？任何建议也将不胜感激。

oracle

来源：https://stackoverflow.com/questions/44504870/create-unique-constraint-over-function-based-index-with-existing-duplicate-value

2条答案

按热度按时间

ijxebb2r1#

这里有一个可能的方法。创建一个实体化视图，在提交时刷新（如果情况允许，最好是快速刷新;在这种情况下，他们应该）。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
;

然后在MV上的（col 1，col 3，col 4）上放置唯一约束。

赞(0）回复(0）举报 2023-10-16

9nvpjoqh2#

触发的想法（如果是暂时的）是一个很好的想法。您可以通过使用两个表和一个视图来进一步扩展它。
这个想法是有你的原始表+一个新的表。新表具有该限制，而原始表没有。视图触发器处理将插入定向到新表中，而不处理原始表。我添加了一个检查，以确保插入不会复制现有的orig记录：如果不需要，你可以在触发器中删除COUNT(*)检查。
完成后，删除原始表和视图，然后将新表重命名为MYTAB。两个表可以让您清楚地看到哪些记录需要清理。

CREATE TABLE MYTAB (COL1 NUMBER, COL2 NUMBER, COL3 NUMBER, COL4 NUMBER);
INSERT INTO MYTAB_ORIG VALUES (1, 1, 1, 1); --OK
INSERT INTO MYTAB_ORIG VALUES (1, 2, 1, 1); -- NOOK
INSERT INTO MYTAB_ORIG VALUES (1, 3, NULL, NULL); --OK
INSERT INTO MYTAB_ORIG VALUES (1, 4, NULL, NULL); --OK

ALTER TABLE MYTAB RENAME TO MYTAB_ORIG;
CREATE TABLE MYTAB_NEW (COL1 NUMBER, COL2 NUMBER, COL3 NUMBER, COL4 NUMBER);

-- copied from your example
CREATE UNIQUE INDEX FN_UIX_MYTAB_NEW
    ON MYTAB_NEW (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 OR REPLACE VIEW MYTAB AS
SELECT * FROM MYTAB_ORIG
UNION ALL
SELECT * FROM MYTAB_NEW;

CREATE OR REPLACE TRIGGER MYTAB_NEW_TRG
INSTEAD OF INSERT
ON MYTAB
FOR EACH ROW
    V_COUNT INTEGER;
BEGIN

    -- make this whatever is appropriate
    SELECT COUNT(*)
     INTO V_COUNT
     FROM MYTAB_ORIG
    WHERE COL1 = :NEW.COL1
      AND COL3 = :NEW.COL3
      AND COL4 = :NEW.COL4
      AND ROWNUM = 1;
      
    IF V_COUNT > 0 THEN
      RAISE DUP_VAL_ON_INDEX;
    END IF;
      
    INSERT INTO MYTAB_NEW (COL1, COL2, COL3, COL4)  
    VALUES (:NEW.COL1, :NEW.COL2, :NEW.COL3, :NEW.COL4);
END;
/

SELECT * FROM MYTAB;

INSERT INTO MYTAB VALUES (1, 1, 1, 1);
INSERT INTO MYTAB VALUES (1, 1, 1, 1);
/*
ORA-00001: unique constraint (FN_UIX_MYTAB_NEW) violated
ORA-06512: at "MYTAB_NEW_TRG", line 2
ORA-04088: error during execution of trigger 'MYTAB_NEW_TRG'

View program sources of error stack?
*/

赞(0）回复(0）举报 2023-10-16

我来回答

oracle 使用现有重复值在基于函数的索引上创建唯一约束

2条答案

相关问题

热门标签

最新问答