oracle - create a view with a primary key

Question

This question is a duplicate in meaning still I have to clarify it. Oracle documentation specifically says that it is possible to specify a primary key in CREATE VIEW clause(11g docs has the same notion) . Yet when I try to do it like this:

create or replace view ABC(A, B, C, CONSTRAINT A_PK PRIMARY KEY (A)) ....

I get ORA-00922: missing or invalid option pointing at "primary key" phrase. The question is, is it me or is it something wrong with Oracle Documentation?

Answer 1

The simple answer is that your syntax is incorrect. You must specify DISABLE.

NOVALIDATE disables 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 novalidate disables the ability to disable the primary key.

The rely is optional; it specifies whether to take into account the primary key when creating the view. The antonym of rely is 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:

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.

Answer 2

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)