Create Unique Constraint over function based index with existing duplicate values

Question

I have a table with wrong data and I'd like to prevent new wrong data from being inserted while I fix data and find out what process or where is the sentence making this happen. I first made a UQ constraint over the columns that shouldn't be duplicated, but this gets me into another problem: I need to apply uniqueness only when all the columns have value, if there are nulls I need duplicate records over these columns. Something like this:

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

If I create a constraint like this:

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

Last insert will crash.

I've tried with

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) ;

But the create crashes because table has duplicate data. I'll need to create this index without validating existing data, which means index will be applied only to new records inserted.

I've tried also with:

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;

But this gives me error:

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

Is there a way to do what I've explained, or should I prevent wrong inserts in another way while I look for the origin of the problem? Any advice will be appreciated also.

If this is a temp workaround until you clean up your tables, maybe use a trigger to prevent new duplicates, then remove and add proper constraint. — OldProgrammer, Jun 12 '17 at 17:11
Your problem statement doesn't quite make sense. You want to create a unique index on col1, col3, col4, but you want to allow (1, ..., NULL, NULL) to be duplicated. This is not what UNIQUE means, on those three columns - the issue is not "old data that violates the uniqueness". Even if you first create the table, then add this unique constraint, and then you populate the table for the first time, you will be unable to add all the rows in your example, EXACTLY for that reason. What does this have to do with pre-existing data? — , Jun 12 '17 at 18:21
@mathguy I'd like UNIQUE to be applied only when these 3 columns have value. This is a temp workaround, as OldProgrammer has pointed, I only need this while I find the origin of the problem and find a clean solution. — EAmez, Jun 13 '17 at 07:05

score 0 · Answer 1 · answered Jun 13 '17 at 10:19

0

Here's one possible approach. Create a materialized view, with refresh on commit (preferably fast refresh, if the circumstances permit; in this case, they should). The MV would be something like

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
;

And then put a unique constraint on (col1, col3, col4) on the MV.

answered Jun 13 '17 at 10:19

But a constraint in the view won't prevent "wrong" data being inserted. Also, I've tried your solution but doesn't work because MyTab doesn't have a PK because columns that make each record unique are columns that sometimes need to be empty. Yeah, I know this is a weird scenario... but it's the way someone else made things long time ago :( – EAmez Jun 13 '17 at 12:16
1

The absence of a PK is indeed an issue. But otherwise, a constraint in the MV **will** prevent "wrong" data being inserted. It's a pretty common strategy for this kind of scenario, I didn't just invent it! It works like this: You try to insert "wrong" data. On commit, the MV must be refreshed. The MV rejects the new row, which in turn invalidates the entire transaction. – Jun 13 '17 at 12:27

score 0 · Answer 2 · answered Aug 25 '23 at 15:08

The trigger idea (if temporary) is a good one. You could expand this further by using two tables and a view.

The idea is to have your original table + a new table. The new table has the restriction and the original table doesn't. The view trigger handles directing inserts into the new table and leaves the orig table alone. I added a check that an insert does not duplicate an existing orig record: if that's not required you can remove the COUNT(*) check in the trigger.

When you are done you drop the original table and the view and then rename the new table to MYTAB. Two tables lets you clearly see what records are left to clean up.

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?
*/

Create Unique Constraint over function based index with existing duplicate values

2 Answers2