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?from which table is it deleted? This section deals with these questions.
To be modifiable (also referred to as updatable), a join view must not contain any of the following:
Hierarchical query clauses, such as START WITH or CONNECT BY
GROUP BY or ORDER BY clauses
MODEL query
Set operations, such as UNION, UNION ALL, INTERSECT, MINUS
Aggregate functions, such as AVG, COUNT, MAX, MIN, SUM, and so on
Analytical functions, such as CUME_DIST, and so on
A subquery or a collection expression in the SELECT list
The DISTINCT operator
WITH READ ONLY option
The ROWNUM pseudocolumn
A DML statement on a join view can modify only one base table of the view. Thus, to be modifiable, a join view must also preserve a key from at least one of its tables.
3.5.1 Key-Preserved Tables
A key-preserved table is the most important requirement 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. Here's an example that better demonstrates the concept of key preserved tables:
DESC employee
Name Null? Type
----------------------------------------- -------- -------------
EMP_ID NOT NULL NUMBER(5)
FNAME VARCHAR2(20)
LNAME VARCHAR2(20)
DEPT_ID NOT NULL NUMBER(5)
MANAGER_EMP_ID NUMBER(5)
SALARY NUMBER(5)
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 OR REPLACE VIEW v_rtlr_emp AS
SELECT c.rtlr_nbr, c.name, c.city, e.emp_id,
c.salesperson_id, e.lname sales_rep
FROM retailer c JOIN employee e
ON c.salesperson_id = e.emp_id;
View created.
SELECT * FROM v_rtlr_emp;
RTLR_NBR NAME CITY EMP_ID SALES_REP
-------- ------------------------------- --------------- ------ -
104 EVERY MOUNTAIN CUPERTINO 7499 ALLEN
107 WOMENS SPORTS SUNNYVALE 7499 ALLEN
201 STADIUM SPORTS NEW YORK 7499 ALLEN
203 REBOUND SPORTS NEW YORK 7499 ALLEN
207 FAST BREAK CONCORD 7499 ALLEN
216 THE ALL AMERICAN CHELSEA 7499 ALLEN
223 VELO SPORTS MALDEN 7499 ALLEN
227 THE TOUR SOMERVILLE 7499 ALLEN
218 THE OUTFIELD FLUSHING 7499 ALLEN
211 AT BAT BROOKLINE 7499 ALLEN
206 THE COLISEUM SCARSDALE 7499 ALLEN
205 POINT GUARD YONKERS 7499 ALLEN
202 HOOPS LEICESTER 7499 ALLEN
101 TKB SPORT SHOP REDWOOD CITY 7521 WARD
228 FITNESS FIRST JACKSON HEIGHTS 7521 WARD
226 CENTURY SHOP HUNTINGTON 7521 WARD
106 SHAPE UP PALO ALTO 7521 WARD
103 JUST TENNIS BURLINGAME 7521 WARD
102 VOLLYRITE BURLINGAME 7654 MARTIN
208 AL AND BOB'S SPORTS AUSTIN 7654 MARTIN
204 THE POWER FORWARD DALLAS 7654 MARTIN
215 BOB'S FAMILY SPORTS HOUSTON 7654 MARTIN
217 HIT name, THROW addr, AND RUN GRAPEVINE 7654 MARTIN
214 AL'S PRO SHOP SPRING 7654 MARTIN
100 JOCKSPORTS BELMONT 7844 TURNER
212 ALL SPORT BROOKLYN 7844 TURNER
221 WHEELS AND DEALS HOUSTON 7844 TURNER
224 JOE'S BIKE SHOP GRAND PRAIRIE 7844 TURNER
225 BOB'S SWIM, CYCLE AND RUN IRVING 7844 TURNER
222 JUST BIKES DALLAS 7844 TURNER
213 GOOD SPORT SUNNYSIDE 7844 TURNER
105 K + T SPORTS SANTA CLARA 7844 TURNER
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 you select the key column(s) of a table in the view definition, your doing so 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.
A join view may SELECT data from many tables. However, any DML operation can modify the data from only one underlying table. 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 the following 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 viewThis 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.
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.
But wait! The view joined two tables? What row then, did we just delete? The answer is that we deleted a row from the key-preserved table, in this case from the retailer table.
Let's take another example where there is more than one key-preserved table. We will create a join view that involves two key-preserved tables, and then attempt to delete from that view.
CREATE VIEW v_cust_disputed_ord AS SELECT d.order_nbr, d.cust_nbr, c.cancelled_dt FROM disputed_orders d JOIN cust_order c ON d.order_nbr = c.order_nbr; View created.
In the view v_cust_disputed_ord both the tables are key-preserved, because the key of each of the tables is also a key of the result set of the join. Now try deleting a row from this view:
DELETE FROM v_cust_disputed_ord WHERE order_nbr = 1003; 1 row deleted.
Since there are two key-preserved tables, which table did the row get deleted from? After querying the individual tables, you will find that a row has been deleted from the disputed_orders table. Why? This is a bit tricky. The rule is that if you attempt to delete a row from a join view having more than one key-preserved table, the row will be deleted from the first table in the join. If you reverse the order of the tables in the join, and then issue a delete, you will find that the row will be deleted from the cust_order table. This is strange, but it's the rule. Keep this unexpected behavior in mind when you write applications that need to delete from a join view with multiple key-preserved tables.
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 = 215; 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 table3.5.5 Data Dictionary Views to Find Updatable 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 updatable. 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)
|
The following example shows USER_UPDATABLE_COLUMNS being queried for a list of updatable columns in the v_rtlr_emp view:
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 EMP_ID NO NO NO DEMO V_RTLR_EMP SALESPERSON_ID YES YES YES DEMO V_RTLR_EMP SALES_REP NO NO NO
3.5.6 Impact of WITH CHECK OPTION
WITH CHECK OPTION is an optional clause in the CREATE VIEW statement that prevents any changes to the data in the view that could cause rows to be not included in the view. For example, you have a view with the following definition:
CREATE VIEW emp_20 AS SELECT * FROM employee WHERE dept_id = 20 WITH CHECK OPTION;
Using this view you can't insert a row that has dept_id = 30, or update the existing rows to have dept_id = 30, as shown in the following example:
INSERT INTO emp_20 VALUES
(8765, 'SANJAY','MISHRA', 30, 7656, 4000, '01-JAN-88', 765);
INSERT INTO emp_20 VALUES
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
UPDATE emp_20 SET dept_id = 30;
UPDATE emp_20 SET dept_id = 30
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violationSince the WHERE clause of the view definition restricts the data in the view to dept_id = 20, and the view is defined with the clause WITH CHECK OPTION, you are not allowed to insert or update rows that could cause the rows not to be included in this view.
The purpose of WITH CHECK OPTION is to prevent DML operations as shown in the preceding example. However, this clause has some side effects on the updatability of join views in general.
If a join view is created using the WITH CHECK OPTION clause, INSERT statements are not allowed on the view at all, 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 JOIN employee e
ON 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 hereThe error message "ORA-01733: virtual column not allowed here" may not be very comprehensible, but it indicates that you are not allowed to insert into this join view.
WITH CHECK OPTION as such doesn't prevent you from deleting rows from a join view, as shown in the following example:
DELETE FROM v_rtlr_emp_wco WHERE rtlr_nbr = 215; 1 row deleted.
However, WITH CHECK OPTION prevents deletion if the join view involves a self join of the key-preserved table. For example, the view emp_mgr_wco involves a self join of the table employee (which is the key-preserved table in this view definition).
CREATE VIEW emp_mgr_wco AS
SELECT e.lname employee, e.salary salary, m.lname manager
FROM employee e, employee m
WHERE e.manager_emp_id = m.emp_id
WITH CHECK OPTION;
View created.
DELETE FROM emp_mgr_wco WHERE employee = 'JONES';
DELETE FROM emp_mgr_wco WHERE employee = 'JONES'
*
ERROR at line 1:
ORA-01752: cannot delete from view without exactly one key-preserved tableYou get an error while trying to delete a row from the view emp_mgr_wco, as it involves a self join of the key-preserved table.
Furthermore, the WITH CHECK OPTION restricts your 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 tables involved in a self join. The following example illustrates the error you get when trying to update the join column of such a view.
UPDATE v_rtlr_emp_wco
SET salesperson_id = 7784
WHERE rtlr_nbr = 215;
SET salesperson_id = 7784
*
ERROR at line 2:
ORA-01733: virtual column not allowed hereThe error message "ORA-01733: virtual column not allowed here" indicates that you are not allowed to update the indicated column. Since the view is created with the WITH CHECK OPTION clause, and the column salesperson_id is a join column, you are not allowed to update it. You will get a similar error if you try to update a column of a table involved in a self join, as illustrated in the following example:
UPDATE emp_mgr_wco
SET salary = 4800
WHERE employee = 'JONES';
SET salary = 4800
*
ERROR at line 2:
ORA-01733: virtual column not allowed hereIn this example, since the view definition involves a self join of the employee table, and the view is created with the WITH CHECK OPTION clause, you are not allowed to update any columns of the employee table.
