7.4 How do I…Create an updatable join view?

Problem

I want to create views based upon my tables and objects, and would like to allow the underlying tables to be modified. My application requires that the tables and objects can be inserted, updated, and deleted, yet I want to retain the flexibility and security of using views. How do I create an updatable join view?

Technique

When a view is based upon a single table or object, all Data Manipulation Language statements (DML) may be performed on the view such as insert, update, and delete, if the user account has proper permissions. However, when two or more tables are joined within a view, there are limitations on if and how the underlying tables may be inserted, updated, and deleted.

Only one table may be modified, and with certain restrictions. The most important condition is that the table being modified is key-preserved. A key-preserved table has all of its identifying unique key columns contained within the SELECT clause of the view text. For the view to be updatable, the resulting join must also uniquely define each record in the view. This How-To will provide examples that explain this important condition.

In addition to having to be key-preserved, the view must not contain certain clauses. These are the ROWNUM pseudocolumn, table hierarchies (START WITH; CONNECT BY), set operations (UNION, MINUS, and so on), grouping functions (DISTINCT; GROUP BY; HAVING), and math functions (COUNT, MAX, SUM, and so on).

To delete records, only one table in the resulting join may be key-preserved. If two or more tables are key-preserved, Oracle would not know which table to delete from, due to the ambiguity of the statement. To insert records, all columns of the view must come from key-preserved tables. If a view has several columns from several tables, and at least one column is from an underlying non–key-preserved table, then Oracle would not be able to determine how to uniquely identify the record being inserted.

As with inserting records, to update records, all columns of the view must come from key-preserved tables. If a view has several columns from several tables, and at least one column is from an underlying non–key-preserved table, then Oracle would not be able to determine how to uniquely identify the record being updated.

SQL> START CHP7_11.SQL

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

SQL>

Steps

1. Run SQL*Plus and connect as the WAITE user account. CHP7_10.SQL creates the PART07 and FACTORY07 tables. Run the script, as shown in Figure 7.5.

Notice that the FACTORY_NO column of the FACTORY07 table is defined as the primary key. If the FACTORY_NO column appears in the view, it will be an updatable key-defined view. Also, the PARTNO column of the PART07 table will be the key-defined column whose inclusion in the view is necessary for updating data.

2. Run the CHP7_11.SQL script to insert data into each table. The data will be used to describe how views may be updated. CHP7_11.SQL is shown in Figure 7.6.

SQL> START CHP7_11.SQL

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

SQL>

3. Now that the two tables have data, run the CHP7_12.SQL script to create the PART_VIEW07 view, which is a join of the PART07 and FACTORY07 tables. This process is shown in Figure 7.7.

4. The PART_VIEW07 view has been created. Note that because of the many-to-one relationship between PART07 and FACTORY07, only the PART07 table may be updated. In our example, there are three records that exist where the FACTORY_NO=20. If the entire operation were to move to Tokyo, we would want to update all records to point to Japan. Because the FACTORY07 is not a key-preserved table in this join view, the statement will fail. Run CHP7_13.SQL to see the results.

SQL> GET CHP7_13.SQL

1 UPDATE PART_VIEW07

2 SET LOCATION=’TOKYO, JAPAN’

SQL> /

SET LOCATION=’TOKYO, JAPAN’

ERROR at line 2:

ORA-01779: cannot modify a column which maps to a non key-preserved table

SQL>

5. You may, however, update the PART07 table, because it is a key-preserved table and also uniquely identifies each record in the PART_VIEW07 view. In our example, assume that all of the horses escaped, and we must update the PART07 to reflect this. Run the CHP7_14.SQL script to set the QTY=0 for the HORSE record.

SQL> GET CHP7_14.sql

1 UPDATE PART_VIEW07

2 SET QTY=0

3 WHERE PART_NAME = ‘HORSE’

SQL> /

1 row updated.

How It Works

Step 1 creates the PART07 and FACTORY07 tables. PART07 is uniquely defined by a primary key on PARTNO, and FACTORY07 is uniquely defined by a primary key on FACTORY_NO, and the two tables are joined by a many-to-one relationship based upon the FACTORY_NO column in each table. Step 2 inserts sample data into both tables. Step 3 creates the PART_VIEW07 view, based on a join of the two tables. Step 4 tries and fails to update a column on the FACTORY07 table, which is not a key-preserved table. Step 5 successfully updates the PART07 table through the view, because the PART07 table is key-preserved in the PART_VIEW07 view.

Comments

A view constructed by an outer join may be updatable if it is still key-preserved. Each record must be uniquely identifiable, and no keys may contain NULL values. For object views, you may also update them with INSTEAD OF triggers. See How-To 16.9 on the use of INSTEAD OF triggers.

You may create UNIQUE indexes on the key-preserved columns instead of PRIMARY/FOREIGN constraints shown in this How-To.