Oracle doesn't let you delete from a view unless it's simply a view on a single table. Consider:
Connected to:
Oracle8i Release 8.1.6.1.0 - Production
JServer Release 8.1.6.0.0 - Production
SQL> create table foo ( x integer primary key );
Table created.
SQL> create table bar ( y integer primary key );
Table created.
SQL> create view foobar as select x, y from foo, bar;
View created.
SQL> create view foofoo as select x from foo;
View created.
SQL> insert into foo values (1);
1 row created.
SQL> insert into bar values (2);
1 row created.
SQL> delete from foobar;
delete from foobar
*
ERROR at line 1:
ORA-01752: cannot delete from view without exactly one key-preserved table
SQL> delete from foofoo;
1 row deleted.
You can, however (since Oracle 8), define a
instead-of trigger to handle the delete. I'd imagine you can do something simillar with Postgres's
create rule.
create trigger foobar_trigger
instead of delete on foobar
for each row
begin
delete from foo where x = :old.x;
delete from bar where y = :old.y;
end foobar_trigger;
Now, let's see it work:
SQL> insert into foo values (3);
1 row created.
SQL> delete from foobar;
1 row deleted.
