Как не допустить вставки значений в две колонки если оба эти значения уже существуют в соответствующей колонке?

Question

Есть такая таблица:

CREATE TABLE MYTABLE (
    id INT NOT NULL AUTO_INCREMENT,
    Field1 VARCHAR(30),
    Field2 NUMBER(10),
);

Следующие вставки должны сработать, так как одно из значений, Field1 или Field2, ешё не существует в соответствующей колонке:

INSERT INTO MYTABLE VALUES(null, 'Value', 10);
INSERT INTO MYTABLE VALUES(null, 'Value', 5);
INSERT INTO MYTABLE VALUES(null, 'Something', 10);

Но следующая вставка не должна сработать потому, что оба значения, Field1='Something' и Field2=5, уже существуют в соответствующей колонке:

INSERT INTO MYTABLE VALUES(null, 'Something', 5);

Как можно добиться такого поведения?

Думал об использовании SQL Assertions, но они еще не реализованы в Oracle.

_{Свободный перевод вопроса How can I disable entering the same values in two fields at the same time on a table? от участника @Fiorella}

Answer 1

Одно из возможных решений - COMPOUND триггер.

Таблица:

create table tab (
    id int generated always as identity primary key,
    col1 varchar2 (16),
    col2 number)
/
insert into tab (col1, col2) 
    select trim (column_value), rownum from xmlTable ('"a","b","c"');

Вспомогательный пакет:

create or replace package tabapi as
    type rec is record (
        rid varchar2 (18), col1 varchar2 (32), col2 number); 
    type arr is table of rec;
    procedure assert (t arr);     
end;
/
create or replace package body tabapi as
    procedure assert (t arr) is 
    begin 
        for r in (
            select t.*
            from table (t) t 
            join tab t1 on t1.col1 = t.col1 and t1.rowid != chartorowid (t.rid) 
            join tab t2 on t2.col2 = t.col2 and t2.rowid != chartorowid (t.rid)
            fetch first 1 row only
        ) loop raise_application_error (
            -20000, 'Found cross-row duplicates ('||r.col1||','||r.col2||')');
        end loop;   
    end; 
end;
/

Сам триггер:

create or replace trigger trigTabCheckCrossRowDuplicates 
for update or insert on tab compound trigger
    rows tabapi.arr := tabapi.arr ();
    after each row is    
    begin
        rows.extend;
        rows (rows.count) := tabapi.rec (
            rowidtochar (:new.rowid), :new.col1, :new.col2);
    end after each row;
    after statement is
    begin
        tabapi.assert (rows);
    end after statement;    
end;
/

Тест:

select * from tab
/
        ID COL1                   COL2
---------- ---------------- ----------
         1 a                         1
         2 b                         2
         3 c                         3

insert into tab (col1, col2) values ('a', 4);

1 row inserted.

insert into tab (col1, col2) values ('c', 1);

Error report -
ORA-20000: Found cross row duplicates (c,1)
ORA-06512: at "DB.TABAPI", line 10

На db<>fidle.

БЛОГ НА HUSL

Как не допустить вставки значений в две колонки если оба эти значения уже существуют в соответствующей колонке?

Ответы (1 шт):