oracle相同列,如何防止列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的值(即：通常它不应该阻止并发的不相交活动).

只会在COMMIT时检查unicity,并且某些工具不会指望提交失败并且可能表现不当.此外,当COMMIT失败时,整个事务将回滚,您将丢失任何未经修改的更改(您无法“重试”).