oracle - 创建一个带有主键的视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9160182/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
oracle - create a view with a primary key
提问by Andrey Balaguta
This question is a duplicate in meaning still I have to clarify it. Oracle documentation specifically saysthat it is possible to specify a primary key in CREATE VIEWclause(11g docs has the same notion) . Yet when I try to do it like this:
这个问题在意义上是重复的，我仍然必须澄清它。Oracle 文档特别指出可以在CREATE VIEW子句中指定主键（11g 文档具有相同的概念）。然而，当我尝试这样做时：
create or replace view ABC(A, B, C, CONSTRAINT A_PK PRIMARY KEY (A)) ....
I get ORA-00922: missing or invalid optionpointing at "primary key" phrase. The question is, is it me or is it something wrong with Oracle Documentation?
我ORA-00922: missing or invalid option指着“主键”短语。问题是，是我还是 Oracle 文档有问题？
回答by Ben
The simple answer is that your syntax is incorrect. You mustspecify DISABLE.
简单的答案是您的语法不正确。您必须指定DISABLE.
NOVALIDATEdisables validation of the primary key, in a view this is the default and so is included automatically; but it's a lot clearer if you use it, as, in a lovely double negative, disable novalidatedisables the ability to disable the primary key.
NOVALIDATE禁用主键的验证，在视图中这是默认值，因此会自动包含在内；但是如果你使用它会更清楚，因为在一个可爱的双重否定中，disable novalidate禁用禁用主键的能力。
The relyis optional; it specifies whether to take into account the primary key when creating the view. The antonym of relyis norely.
该rely是可选的; 它指定在创建视图时是否考虑主键。的反义词rely是norely。
There are a lot of restrictions on creating a view constraint and as it relies on the table below it's not really worth it as already noted by @RC. But if you need it for documentation only then here you go:
创建视图约束有很多限制，因为它依赖于下表，正如@RC 已经指出的那样，它并不值得。但是，如果您只需要将其用于文档，那么您就可以：
SQL> create table tmp_test ( a number(10), b varchar2(120) );
Table created.
SQL>
SQL> insert into tmp_test
2 select level, 'b'
3 from dual
4 connect by level <= 20
5 ;
20 rows created.
SQL> commit ;
Commit complete.
SQL>
SQL> alter table tmp_test
2 add constraint tmp_test_pk
3 primary key (a)
4 using index;
Table altered.
SQL>
SQL> create or replace view v_tmp_test (a, b
2 , constraint v_tmp_test_pk primary key (a) rely disable novalidate) as
3 select a, b
4 from tmp_test
5 ;
View created.
SQL>
From the documentation:
从文档：
View Constraints
Oracle does not enforce view constraints. However, operations on views are subject to the integrity constraints defined on the underlying base tables. This means that you can enforce constraints on views through constraints on base tables.
Notes on View Constraints View constraints are a subset of table constraints and are subject to the following restrictions:
You can specify only unique, primary key, and foreign key constraints on views. However, you can define the view using the WITH CHECK OPTION clause, which is equivalent to specifying a check constraint for the view.
View constraints are supported only in DISABLE NOVALIDATE mode. You cannot specify any other mode. You must specify the keyword DISABLE when you declare the view constraint. You need not specify NOVALIDATE explicitly, as it is the default.
The RELY and NORELY parameters are optional. View constraints, because they are unenforced, are usually specified with the RELY parameter to make them more useful. The RELY or NORELY keyword must precede the DISABLE keyword. Please refer to "RELY Clause" for more information.
Because view constraints are not enforced directly, you cannot specify INITIALLY DEFERRED or DEFERRABLE.
You cannot specify the using_index_clause, the exceptions_clause clause, or the ON DELETE clause of the references_clause.
You cannot define view constraints on attributes of an object column.
查看约束
Oracle 不强制执行视图约束。但是，对视图的操作受在基础基表上定义的完整性约束的约束。这意味着您可以通过对基表的约束来强制对视图进行约束。
关于视图约束的注意事项视图约束是表约束的子集，并受以下限制：
您只能对视图指定唯一、主键和外键约束。但是，您可以使用 WITH CHECK OPTION 子句定义视图，这等效于为视图指定检查约束。
视图约束仅在 DISABLE NOVALIDATE 模式下受支持。您不能指定任何其他模式。声明视图约束时必须指定关键字 DISABLE。您不需要明确指定 NOVALIDATE，因为它是默认值。
RELY 和 NORELY 参数是可选的。视图约束，因为它们是非强制的，通常用 RELY 参数指定，以使它们更有用。RELY 或 NORELY 关键字必须位于 DISABLE 关键字之前。有关更多信息，请参阅“RELY 条款”。
因为视图约束不是直接强制执行的，所以不能指定 INITIALLY DEFERRED 或 DEFERRABLE。
您不能指定references_clause 的using_index_clause、exceptions_clause 子句或ON DELETE 子句。
您不能在对象列的属性上定义视图约束。
回答by RC.
You have to create the constraint as disabled. It is really a way to give a hint to the optimizer to influence the query plan.
您必须将约束创建为禁用状态。它确实是一种给优化器以影响查询计划的提示。
Data integrity is enforced at the underlying table level. When you think about it, enforcing a primary key constraint at the view level doesn't make a whole lot of sense. A plain ole view doesn't store data, it is just a "view" of data provided by other tables. If a primary key constraint was only placed on a view of some underlying table and that table does not enforce the constraint itself, how would the view handle the situation where someone updated the table directly with data that breaks it's constraint? (i.e. the table has no idea what constraints are placed on it via a view)
数据完整性在底层表级别强制执行。仔细想想，在视图级别强制执行主键约束并没有多大意义。普通的 ole 视图不存储数据，它只是其他表提供的数据的“视图”。如果主键约束仅放置在某个基础表的视图上，并且该表本身不强制执行约束，那么视图将如何处理有人直接使用破坏其约束的数据更新表的情况？（即该表不知道通过视图对其施加了哪些约束）
