3.5 DML Statements on a Join View
A join view is a view based on a join. Special
considerations apply when you issue a DML (INSERT, UPDATE, or DELETE)
statement against a join view. Ever thought about what happens when
you insert a row into a join view—which table does the row go
into? And what happens when you delete a row from a join
view—which table does it gets deleted from? This section deals
with these questions.
To be modifiable, a join view must not contain any of the following:
Hierarchical query clauses, such as START WITH or CONNECT BY GROUP BY or HAVING clauses Set operations, such as UNION, UNION ALL, INTERSECT, MINUS Aggregate functions, such as AVG, COUNT, MAX, MIN, SUM, and so forth The DISTINCT operator The ROWNUM pseudocolumn
A DML statement on a join view can modify only one base table of the
view. Apart from these rules, therefore, a join view must also have
one key preserved table to be modified.
3.5.1 Key-Preserved Tables
A key-preserved
table is the most important
requirement in order for a join view to be modifiable. In a join, a
table is called a key-preserved table if its
keys are preserved through the join—every
key of the table can also be a key
of the resultant join result set. Every primary key or unique key
value in the base table must also be unique in the result set of the
join. Let's take an example to understand the
concept of key preserved tables better.
DESC EMPLOYEE
Name Null? Type
------------------------------- -------- ----
EMP_ID NOT NULL NUMBER(4)
LNAME VARCHAR2(15)
FNAME VARCHAR2(15)
DEPT_ID NUMBER(2)
MANAGER_EMP_ID NUMBER(4)
SALARY NUMBER(7,2)
HIRE_DATE DATE
JOB_ID NUMBER(3)
DESC RETAILER
Name Null? Type
------------------------------- -------- ----
RTLR_NBR NOT NULL NUMBER(6)
NAME VARCHAR2(45)
ADDRESS VARCHAR2(40)
CITY VARCHAR2(30)
STATE VARCHAR2(2)
ZIP_CODE VARCHAR2(9)
AREA_CODE NUMBER(3)
PHONE_NUMBER NUMBER(7)
SALESPERSON_ID NUMBER(4)
CREDIT_LIMIT NUMBER(9,2)
COMMENTS LONG
CREATE VIEW V_RTLR_EMP AS
SELECT C.RTLR_NBR, C.NAME, C.CITY, E.EMP_ID, E.LNAME SALES_REP
FROM RETAILER C, EMPLOYEE E
WHERE C.SALESPERSON_ID = E.EMP_ID;
View created.
SELECT * FROM V_RTLR_EMP;
RTLR_NBR NAME CITY EMP_ SALES_REP
----------- ---------------------------- ------------------------------
100 JOCKSPORTS BELMONT 7844 TURNER
101 TKB SPORT SHOP REDWOOD CITY 7521 WARD
102 VOLLYRITE BURLINGAME 7654 MARTIN
103 JUST TENNIS BURLINGAME 7521 WARD
104 EVERY MOUNTAIN CUPERTINO 7499 ALLEN
105 K + T SPORTS SANTA CLARA 7844 TURNER
106 SHAPE UP PALO ALTO 7521 WARD
107 WOMENS SPORTS SUNNYVALE 7499 ALLEN
201 STADIUM SPORTS NEW YORK 7557 SHAW
202 HOOPS LEICESTER 7820 ROSS
203 REBOUND SPORTS NEW YORK 7557 SHAW
204 THE POWER FORWARD DALLAS 7560 DUNCAN
205 POINT GUARD YONKERS 7557 SHAW
206 THE COLISEUM SCARSDALE 7557 SHAW
207 FAST BREAK CONCORD 7820 ROSS
208 AL AND BOB'S SPORTS AUSTIN 7560 DUNCAN
211 AT BAT BROOKLINE 7820 ROSS
212 ALL SPORT BROOKLYN 7600 PORTER
213 GOOD SPORT SUNNYSIDE 7600 PORTER
214 AL'S PRO SHOP SPRING 7564 LANGE
215 BOB'S FAMILY SPORTS HOUSTON 7654 MARTIN
216 THE ALL AMERICAN CHELSEA 7820 ROSS
217 HIT, THROW, AND RUN GRAPEVINE 7564 LANGE
218 THE OUTFIELD FLUSHING 7820 ROSS
221 WHEELS AND DEALS HOUSTON 7789 WEST
222 JUST BIKES DALLAS 7789 WEST
223 VELO SPORTS MALDEN 7820 ROSS
224 JOE'S BIKE SHOP GRAND PRARIE 7789 WEST
225 BOB'S SWIM, CYCLE, AND RUN IRVING 7789 WEST
226 CENTURY SHOP HUNTINGTON 7555 PETERS
227 THE TOUR SOMERVILLE 7820 ROSS
228 FITNESS FIRST JACKSON HEIGHTS 7555 PETERS
32 rows selected.
The view V_RTLR_EMP is a join of RETAILER and EMPLOYEE tables on the
RETAILER.SALESPERSON_ID and EMPLOYEE.EMP_ID columns. Is there a
key-preserved table in this join view? Which one—or is it both?
If you observe the relationship between the two tables and the join
query, you will notice that RTLR_NBR is the key of the RETAILER
table, as well as the key of the result of the join. This is because
there is only one row in the RETAILER table for every row in the join
view V_RTLR_EMP and every row in the view has a unique RTLR_NBR.
Therefore, the table RETAILER is a key-preserved table in this join
view. How about the EMPLOYEE table? The key of the EMPLOYEE table is
not preserved through the join because EMP_ID is not unique in the
view, consequently EMP_ID can't be a key for the
result of the join. Therefore, the table EMPLOYEE is not a
key-preserved table in this view.
You must remember the following important points regarding
key-preserved tables:
Key-preservation is a property of the table inside the join view, not
the table itself independently. A table may be key-preserved in one
join view, and may not be key-preserved in another join view. For
example, if we create a join view by joining the EMPLOYEE table with
the DEPARTMENT table on the DEPT_ID column, then in the resulting
view the EMPLOYEE table will be key-preserved, but the DEPARTMENT
table will not be a key-preserved table.
It is not necessary for the key column(s) of a table to be SELECTed
in the join view for the table to be key-preserved. For example, in
the V_RTLR_EMP view discussed previously, the RETAILER table would
have been the key-preserved table even if we had not included the
RTLR_NBR column in the SELECT list.
On the other hand, if we select the key column(s) of a table in the
view definition, it doesn't make that table
key-preserved. In the V_RTLR_EMP view, even though we have included
EMP_ID in the SELECT list, the EMPLOYEE table is not key-preserved.
The key-preserved property of a table in a join view
doesn't depend on the data inside the table. It
depends on the schema design and the
relationship between the tables.
The following sections discuss how you can use INSERT, UPDATE, and
DELETE statements on a join view.
3.5.2 INSERT Statements on a Join View
Let's issue an INSERT statement
against the join view V_RTLR_EMP that
attempts to insert a record into the RETAILER table:
INSERT INTO V_RTLR_EMP (RTLR_NBR, NAME, SALESPERSON_ID)
VALUES (345, 'X-MART STORES', 7820);
1 row created.
That worked. Now let's try this INSERT statement,
which also supplies a value for a column from the EMPLOYEE table:
INSERT INTO V_RTLR_EMP (RTLR_NBR, NAME, SALESPERSON_ID, SALES_REP)
VALUES (456, 'LEE PARK RECREATION CENTER', 7599, 'JAMES');
INSERT INTO V_RTLR_EMP (RTLR_NBR, NAME, SALESPERSON_ID, SALES_REP)
*
ERROR at line 1:
ORA-01776: cannot modify more than one base table through a join view
This INSERT statement attempts to insert values into two tables
(RETAILER and EMPLOYEE), which is not allowed. You
can't refer to the columns of a non-key-preserved
table in an INSERT statement. Moreover, INSERT statements are not
allowed on a join view if the view is created using the
WITH CHECK OPTION
clause, even if you are attempting to insert into the key-preserved
table only. For example:
CREATE VIEW V_RTLR_EMP_WCO AS
SELECT C.RTLR_NBR, C.NAME, C.CITY, C.SALESPERSON_ID, E.LNAME SALES_REP
FROM RETAILER C, EMPLOYEE E
WHERE C.SALESPERSON_ID = E.EMP_ID
WITH CHECK OPTION;
View created.
INSERT INTO V_RTLR_EMP_WCO (RTLR_NBR, NAME, SALESPERSON_ID)
VALUES (345, 'X-MART STORES', 7820);
INSERT INTO V_RTLR_EMP_WCO (RTLR_NBR, NAME, SALESPERSON_ID)
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
The error message "ORA-01733: virtual column not
allowed here" may not be very clear, but it
indicates that you are not allowed to insert into this join view.
3.5.3 DELETE Statements on a Join View
DELETE operations can be
performed on a join view if the join view
has one and only one key-preserved table. The view V_RTLR_EMP
discussed previously has only one key-preserved table, RETAILER;
therefore, you can delete from this join view as in the following
example:
DELETE FROM V_RTLR_EMP
WHERE RTLR_NBR = 214;
1 row deleted.
Let's take another example where there is more than
one key-preserved table. We will create a view from the self join
example we discussed earlier in this chapter and attempt to delete
from the view.
CREATE VIEW V_DEPT_TEAM AS
SELECT D1.NAME TEAM1, D2.NAME TEAM2
FROM DEPARTMENT D1, DEPARTMENT D2
WHERE D1.DEPT_ID > D2.DEPT_ID;
View created.
DELETE FROM V_DEPT_TEAM
WHERE TEAM1 = 'SALES';
DELETE FROM V_DEPT_TEAM
*
ERROR at line 1:
ORA-01752: cannot delete from view without exactly one key-preserved table
3.5.4 UPDATE Statements on a Join View
An UPDATE operation
can be performed on a join view if it
attempts to update a column in the key-preserved table. For example:
UPDATE V_RTLR_EMP
SET NAME = 'PRO SPORTS'
WHERE RTLR_NBR = 214;
1 row updated.
This UPDATE is successful since it updated the NAME column of the
RETAILER table, which is key-preserved. However, the following UPDATE
statement will fail because it attempts to modify the SALES_REP
column that maps to the EMPLOYEE table, which is non-key-preserved:
UPDATE V_RTLR_EMP
SET SALES_REP = 'ANDREW'
WHERE RTLR_NBR = 214;
SET SALES_REP = 'ANDREW'
*
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table
The WITH CHECK OPTION further restricts the ability to modify a join
view. If a join view is created using the WITH CHECK OPTION clause,
you can't modify any of the join columns, nor any of
the columns from the repeated tables:
UPDATE V_RTLR_EMP_WCO
SET SALESPERSON_ID = 7784
WHERE RTLR_NBR = 214;
SET SALESPERSON_ID = 7784
*
ERROR at line 2:
ORA-01733: virtual column not allowed here
The error message "ORA-01733: virtual
column not
allowed here" indicates that you are not allowed to
update the indicated column.
3.5.5 Data Dictionary Views to Find Updateable Columns
Oracle provides the
data
dictionary view USER_UPDATABLE_COLUMNS
that shows all modifiable columns in all tables and views in a
user's schema. This can be helpful if you have a
view that you wish to update, but aren't sure
whether it's updateable. USER_UPDATABLE_COLUMNS has
the following definition:
DESC USER_UPDATABLE_COLUMNS
Name Null? Type
-------------- -------- -------------
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
COLUMN_NAME NOT NULL VARCHAR2(30)
UPDATABLE VARCHAR2(3)
INSERTABLE VARCHAR2(3)
DELETABLE VARCHAR2(3)
 |
ALL_UPDATABLE_COLUMNS shows all views you
can access (as opposed to just those you own), and
DBA_UPDATABLE_COLUMNS (for DBAs only) shows
all views in the database.
|
|
The following example shows the view being queried for a list of
updateable columns in the V_RTLR_EMP_WCO view:
SELECT * FROM USER_UPDATABLE_COLUMNS
WHERE TABLE_NAME = 'V_RTLR_EMP_WCO';
OWNER TABLE_NAME COLUMN_NAME UPD INS DEL
------- ---------------- ---------------- --- --- ---
DEMO V_RTLR_EMP_WCO RTLR_NBR YES YES YES
DEMO V_RTLR_EMP_WCO NAME YES YES YES
DEMO V_RTLR_EMP_WCO CITY YES YES YES
DEMO V_RTLR_EMP_WCO SALESPERSON_ID NO NO NO
DEMO V_RTLR_EMP_WCO SALES_REP NO NO NO
Compare the updateable columns of the view V_RTLR_EMP_WCO with those
of the view V_RTLR_EMP:
SELECT * FROM USER_UPDATABLE_COLUMNS
WHERE TABLE_NAME = 'V_RTLR_EMP';
OWNER TABLE_NAME COLUMN_NAME UPD INS DEL
------- ------------- ---------------- --- ---
DEMO V_RTLR_EMP RTLR_NBR YES YES YES
DEMO V_RTLR_EMP NAME YES YES YES
DEMO V_RTLR_EMP CITY YES YES YES
DEMO V_RTLR_EMP SALESPERSON_ID YES YES YES
DEMO V_RTLR_EMP SALES_REP NO NO NO
Notice that the column SALESPERSON_ID is modifiable in V_RTLR_EMP,
but not in V_RTLR_EMP_WCO.
|
