oracle - 如何防止Oracle中的b列包含与任何a列相同的值？

Question

有什么好方法可以防止具有 2 列(a(唯一)和 b)的表中出现 b 列等于 a 列中的任何值的任何记录？这将用于像这样的更正表，

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

我可以看到如何使用触发器和子查询来完成它(假设没有并发事件)，但更具声明性的方法会更好。

这是应该防止的示例，

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

理想情况下，该解决方案适用于并发插入和更新。

Answer 1

您可以使用物化 View 来执行您的要求(使用 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 

唯一索引将确保两列(两行)中不能有相同的值。

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)

它将在提交期间序列化，但仅对 A 列和 B 列的值进行序列化(即:通常它不应阻止并发不相交事件)。

仅在提交时检查唯一性，某些工具预计提交不会失败，并且可能会出现异常行为。此外，当 COMMIT 失败时，整个事务将回滚，并且您将丢失任何未提交的更改(您无法“重试”)。