ORA-00922: missing or invalid option on modifying a view - Oracle

Question

I'm trying to add non-nullable constraint to a column in my view. Here is my query :

alter view myTable add constraint pk_generate2 check(pk is not null);

I tried this one :

alter view myTable add constraint pk_generate2 check(pk is not null) disable;

This didn't work either.

It gives the error given in the title. How can I change the query so that it will work? Thanks.

PS : I suspect that there is no solution because there is no way to add such a constraint to a view. But I'm adding ROWNUM as a primary key to the view in order to make it acceptable by Entity Framework.

I solved the problem by including one of the primary keys in the view. — jason, Apr 20 '17 at 06:28

score 1 · Accepted Answer · answered Apr 19 '17 at 16:04

View Constraints

Oracle Database does not enforce view constraints. However, you can enforce constraints on views through constraints on base tables.

You can specify only unique, primary key, and foreign key constraints on views, and they are supported only in DISABLE NOVALIDATE mode. You cannot define view constraints on attributes of an object column.

Read this article for more details: constraint in Oracle

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

If you're adding rownum as a dummy primary key column, then you should add a primary key constraint, not just a null check constraint - which isn't allowed on a view anyway.

As noted in the documentation, only certain constraint types are allowed, and must be defined with disable and novalidate.

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.
...

So you can do:

-- example view with rownum dummy PK column
create view v42 as
select rownum as pk, table_name
from user_tables d;

View V42 created.
    
alter view v42 add constraint pk_generate2 primary key (pk) disable novalidate;

View V42 altered.

The documentation also points out that

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.

but with a value based on rownum (or row_number()) that isn't going to be a problem.

I have already done what you said. But EF doesn't accept such configuration. — jason, Apr 20 '17 at 05:56

ORA-00922: missing or invalid option on modifying a view - Oracle

2 Answers2