CODEWITHSUNDEEP
databaseoracle11gview
MS Wachasunder
MS Wachasunder

Reputation: 65

Alterations in views

Following is the code for sample table I have created:

SQL> create table a (a1 number(2), a2 number(2) not null);

Table created.

Now suppose I create a view on this table as:

SQL> create view aview as select a1 from a;

View created.

I need the opinions on 3 questions: a. Is there a way to insert values in this view. b. If I drop the view using drop view statement. Is there a way to recover like for tables using flashback? c. If I want to add constarint like primary key to this view it is not allowing me. I want to know why?This is what I tried:

SQL> alter view aview add primary key(a1);
alter view aview add primary key(a1)
                     *
ERROR at line 1:
ORA-00922: missing or invalid option


SQL> alter view aview add primary key a1;
alter view aview add primary key a1
                                 *
ERROR at line 1:
ORA-00906: missing left parenthesis

I am using Oracle 11g.

Upvotes: 0

Views: 164

Answers (1)

Alex Poole
Alex Poole

Reputation: 191285

You can add constraints to a view but the documentation says:

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.

So to create your primary key you'd need:

SQL> alter view aview add primary key(a1) disable novalidate;

view AVIEW altered.

or with a named constraint:

alter view aview add constraint aview_pk primary key(a1) disable novalidate;

But as it isn't enforced you can still have duplicate values in the view, and you can insert new duplicate values.

You can't insert directly into the view as the base table as a not-null constraint on a2, which you aren't providing. You'd get an ORA-01400. If you have a default value you can use for a2 then you could add that to the table definition:

create table a (a1 number(2), a2 number(2) default 0 not null);
create view aview as select a1 from a;
alter view aview add constraint aview_pk primary key(a1) disable novalidate;
insert into aview (a1) values (42);
select * from a;

        A1         A2
---------- ----------
        42          0

Or you could create an instead-of trigger, which is what you'd need to do anyway if the view was more complicated (with joins, for example):

create table a (a1 number(2), a2 number(2) not null);
create view aview as select a1 from a;
alter view aview add constraint aview_pk primary key(a1) disable novalidate;

create trigger aview_trig
instead of insert on aview
begin
  insert into a values (:new.a1, 0);
end;
/

insert into aview (a1) values (42);

select * from a;

        A1         A2
---------- ----------
        42          0

But even with the primary key on the view you can do:

insert into aview (a1) values (42);
select * from a;

        A1         A2
---------- ----------
        42          0 
        42          0

If you want to the table to not allow duplicates you'd need a primary or unique key on that. If you want the table to have duplicates but the view to not sure them, make it distinct:

create view aview as select distinct a1 from a;

... but then the default value won't be enough to allow you to insert into the view (you'd get ORA-01732) and you would have to use an instead of trigger.

If you drop the view it is not held in the recycle bin:

drop view aview;
drop table a;
select type, original_name, object_name, operation from user_recyclebin;

TYPE  ORIGINAL_NAME      OBJECT_NAME                    OPERATION
----- ------------------ ------------------------------ ---------
TABLE A                  BIN$/KaYRJ66eC3gQwEAAH/46Q==$0 DROP

That only keeps objects that contain data. As the view has no data, it's just a predefined query, there is nothing to store really, and it's simple enough to recreate the view from its DDL - you aren't risking losing data as you would with a table drop.

Upvotes: 1

Related Questions