代码之家 › 专栏 › 技术社区 › Janek Bogucki Darrell White

如何防止B列与Oracle中的任何A列包含相同的值?

data-integrity oracle

Janek Bogucki Darrell White · 技术社区 · 16 年前

防止A列(唯一)和B列的表具有B列等于A列中任何值的记录的好方法是什么?这将用于类似这样的更正表,

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

我可以看到如何使用触发器和子查询(假设没有并发活动,但更具声明性的方法更可取)来完成。

这是一个应该预防的例子,

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

理想情况下,解决方案将与并发插入和更新一起工作。

2 回复 | 直到 16 年前

Vincent Malgrat 16 年前

您可以使用物化视图来强制您的需求(用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的值上序列化(即:一般情况下,它不应阻止并发的不联合活动)。

只在提交时检查unicity,有些工具不希望提交失败,并且可能行为不适当。同样,当提交失败时,整个事务将回滚,并且您将丢失任何未提交的更改(不能“重试”)。

Gary Myers 16 年前

考虑会话A插入(“A”、“B”)但不提交,然后会话B插入(“B”、“A”)而不提交。两个会话都看不到另一个会话插入的记录。然后会话提交。

可以序列化,方法是在一个会话插入(插入触发器之前)时锁定整个表,并在插入触发器之后执行签入操作。如果表中有您指示的内容,那么它不应该看到很多活动,因此序列化不会成为问题。