How to do a Cross column unique constraint in SQL (Oracle)

user_280244 0
Viewed 207

How to have a Unique Constraint in Oracle-DB with two columns so that a duplicate must not occur in one or the other.

Assume this table

|id | A | B |
|---|---|---|
| 1 | 1 | 2 |
| 2 | 3 | 4 |

I that a new row is not allowed to have in column "A" a value that duplicate a value from column "A" or "B".

In the example above: I am allowed to add 5 to column "A" but not 1, 2, 3, or 4.

My idea was to do something like:

CREATE UNIQUE INDEX crossTest ON test (
    SELECT t.A AS x FROM test t
    UNION ALL
    SELECT t.B AS x FROM test t
    )

but it does not work because Oracle does not accept this syntax.

The two classic approaches:

  • have two unique constraints CREATE UNIQUE INDEX uidxA ON test A and CREATE UNIQUE INDEX uidxB ON test B does not work because then I could add 2 and 4 to column "A"
  • have a unique constraint of two columns CREATE UNIQUE INDEX uidxB ON test (A, B) because this check only existing pairs.

(Bonus question: it should be allowed that "A" and "B" of the same row can be equals)

SQL scripts for the example

CREATE TABLE test (id NUMBER (10) NOT NULL, a VARCHAR2(12), b VARCHAR2(12));
INSERT INTO test (id,a,b) VALUES(1, '1', '2');
INSERT INTO test (id,a,b) VALUES(2, '3', '4');

INSERT INTO test (id,a,b) VALUES(3, '4', 'x'); -> should fail
INSERT INTO test (id,a,b) VALUES(3, '5', 'x'); -> should work
oracle11g unique oracle sql
4 Answers

@Tejash's answer gave me an idea to avoid locking or serialization. You can create an auxiliary table duet_index to produce the extended data set with all rows. Then a simple trigger will do the trick, including your bonus question.

For example:

create table duet_index (
  n number,
  constraint unique uq1 (n)
);

And then the trigger:

create or replace trigger test_trg
before insert on test
for each row
begin
  insert into duet_index (n) values (:new.a);
  if (:new.a <> :new.b) then
    insert into duet_index (n) values (:new.b);
  end if;
end;

Please consider I'm not proficient at writing Oracle triggers. The syntax can be wrong, but the idea should fly.

user_64361910

I've been working with Oracle for decades now and I don't recall having such a requirement. It makes me nervous about your data model.

What you want to do cannot be done with a single index. Trigger-based approaches are going to have trouble working correctly in all multi-user cases. A materialized-view approach seems promising.

My suggestion is to create a materialized view that refreshes on commit and that contains a concatenation (UNION ALL) of the column A and column B values.

Here is what I mean (see comments in code for more details):

create table test1 ( id number not null primary key, a number, b number );

insert into test1 values ( 1, 1, 2);
insert into test1 values ( 2, 3, 4);
commit;
-- Create a snapshot to allow us to create a REFRESH FAST ON COMMIT snapshot...
create snapshot log on test1 with primary key, rowid;

-- And create that snapshot...  this will be updated when any changes to TEST1 are committed   
create materialized view test1_concat 
refresh fast on commit
as
select t1.rowid row_id, 1 as marker, t1.a concatenation from test1 t1
union all 
select t2.rowid row_id, 2 as marker, t2.b concatenation from test1 t2
-- this next bit allows a = b in single rows (i.e., bonus question)
where t2.a != t2.b;

-- Now, enforce the constraint on our snapshot to prevent cross-column duplicates
create unique index test1_concat_u1 on test1_concat ( concatenation );

-- Test #1  -- column a may equal column b without error (bonus!)
insert into test1 values ( 3, 5, 5);
commit;

-- Test #2 uniqueness enforced    
insert into test1 values ( 4, 6, 1);
-- (no error at this point)
commit;

> ORA-12008: error in materialized view refresh path  ORA-00001: unique
> constraint (APPS.TEST1_CONCAT_U1) violated

Drawbacks

  1. There is a scalability issue here. Oracle will synchronize on the commit. Every working solution to your problem will have this drawback, I believe
  2. You do not get an error until the transaction tries to commit, at which point it is impossible to correct and recover the transaction. I believe you cannot solve this drawback in any solution without making drawback #1 much worse (i.e., without much more extensive and longer-lasting locks on your table).
user_51744360

Not possible using INDEX or CONSTRAINT. You need a trigger, something like this:

CREATE OR REPLACE TRIGGER TEST_TRG
BEFORE INSERT ON TEST
FOR EACH ROW
DECLARE
CNT NUMBER := 0;
BEGIN
SELECT COUNT(1) INTO CNT from TEST
WHERE A = :NEW.A OR B = :NEW.A OR A = :NEW.B OR B = :NEW.B;
IF CNT > 0 THEN
raise_application_error(-20111,'This value is not allowed');
END IF;
END;
user_115656290

I suggest fixing our data model, so the values are in rows rather than columns:

CREATE TABLE test (
    id NUMBER (10) NOT NULL,
    type varchar2(1) check (type in ('A', 'B'),
    value varchar2(12),
    unique (value),
    unique (id, type)
);

The unique constraint is then easy.

user_11440350
Related