DUMB question about updatable views

From: tk <theronk_at_charter.net>
Date: Thu, 7 Aug 2003 08:40:13 -0600
Message-ID: <vj4lbu6rmav585@corp.supernews.com>

Hi Folks:

Going thru the Oracle 8i book (even though I installed 9i), I'm just playing around with Triggers/Views to familiarize myself more with them...

I think the "INSTEAD OF" is pretty darn slick...

I am slightly confused:
I created a view:

CREATE OR REPLACE FORCE VIEW TKOUSEK.STUDENTS_VIEW
(FIRST_NAME, LAST_NAME, ID)

AS
SELECT FIRST_NAME, LAST_NAME, ID FROM STUDENTS WHERE ID = 10005; and then played around by issuing the following update: update students_view set first_name = 'TEST';

I thought I'd get an Oracle error to the tune of "Cannot invoke the update" on a view but
instead, it performed the update....

So then I played around further and modified the view to join to another table

CREATE OR REPLACE FORCE VIEW TKOUSEK.STUDENTS_VIEW
(FIRST_NAME, LAST_NAME, ID, TOTAL_CREDITS)
AS
SELECT FIRST_NAME, LAST_NAME, ID, a.TOTAL_CREDITS FROM STUDENTS, MAJOR_STATS a
where STUDENTS.MAJOR = 'Computer Science' and STUDENTS.MAJOR = a.MAJOR;

and called the update

UPDATE STUDENTS_VIEW SET TOTAL_CREDITS = 21; and got the following:
SQL> UPDATE STUDENTS_VIEW SET TOTAL_CREDITS = 21; UPDATE STUDENTS_VIEW SET TOTAL_CREDITS = 21

                         *

ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table

---

So I guess my question is: "What are the rules governing the user to issue update statements on a view?"
I thought the whole purpose of a view was to be 'read-only'. The "INSTEAD OF" trigger makes sense to me.
But in this case, I did not issue an "INSTEAD OF" trigger. Are you allowed to perform updates on views that
join to other tables or are they restricted to views that gather all of their data from just one table?

thanks and sorry for the stupid question... Received on Thu Aug 07 2003 - 09:40:13 CDT