oracle根据a列查出b列,如何防止B列与Oracle中的任何A列包含相同的值?

您可以使用物化视图来强制您的需求(用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,有些工具不希望提交失败,并且可能行为不适当。同样,当提交失败时,整个事务将回滚,并且您将丢失任何未提交的更改(不能“重试”)。