How to prevent column b containing the same value as any column a in Oracle?

Question

What is a good way to prevent a table with 2 columns, a (unique) and b, from having any record where column b is equal to any value in column a? This would be used for a table of corrections like this,

MR   -> Mr
Prf. -> Prof.
MRs  -> Mrs

I can see how it could be done with a trigger and a subquery assuming no concurrent activity but a more declarative approach would be preferable.

This is an example of what should be prevented,

Wing Commdr. -> Wing Cdr.
Wing Cdr.    -> Wing Commander

Ideally the solution would work with concurrent inserts and updates.

Answer 1

Consider session A inserting ('A', 'B') but not committing, then session B inserts ('B','A') without committing. Neither session can see the record inserted by the other. Then the sessions commit.

You can serialize, by locking the whole table when one session inserts (BEFORE INSERT trigger) and do the check in an AFTER INSERT trigger. If the table has the content you indicate, it shouldn't see a lot of activity so the serialization wouldn't be an issue.

Answer 2

Hi Janek,

You could use a materialized view to enforce your requirements (tested with 10.2.0.1).

SQL> CREATE TABLE t (a VARCHAR2(20) NOT NULL PRIMARY KEY,
  2                  b VARCHAR2(20) NOT NULL);
Table created

SQL> CREATE MATERIALIZED VIEW LOG ON t WITH (b), ROWID INCLUDING NEW VALUES;     
Materialized view log created

SQL> CREATE MATERIALIZED VIEW mv
  2     REFRESH FAST ON COMMIT
  3  AS
  4  SELECT 1 umarker, COUNT(*) c, count(a) cc, a val_col
  5    FROM t
  6   GROUP BY a
  7  UNION ALL
  8  SELECT 2 umarker, COUNT(*), COUNT(b), b
  9    FROM t
 10    GROUP BY b;     
Materialized view created

SQL> CREATE UNIQUE INDEX idx ON mv (val_col);     
Index created 

The unique index will ensure that you can not have the same value in both columns (on two rows).

SQL> INSERT INTO t VALUES ('Wing Commdr.', 'Wing Cdr.');     
1 row inserted

SQL> COMMIT;     
Commit complete

SQL> INSERT INTO t VALUES ('Wing Cdr.', 'Wing Commander');     
1 row inserted

SQL> COMMIT;     

ORA-12008: erreur dans le chemin de régénération de la vue matérialisée
ORA-00001: violation de contrainte unique (VNZ.IDX)

SQL> INSERT INTO t VALUES ('X', 'Wing Commdr.');     
1 row inserted

SQL> COMMIT;

ORA-12008: erreur dans le chemin de régénération de la vue matérialisée
ORA-00001: violation de contrainte unique (VNZ.IDX)

It will serialize during commit but only on the values of columns A and B (ie: in general it should not prevent concurrent disjoint activity).

The unicity will only be checked at COMMIT time and some tools don't expect commit to fail and may behave inappropriately. Also when the COMMIT fails, the entire transaction is rolled back and you lose any uncommited changes (you can not "retry").