Oracle Updatable View

Summary: in this tutorial, you will learn about Oracle updatable view and how to insert or update data in the base tables through a view.

A view behaves like a table because you can query data from it. However, you cannot always manipulate data via views. A view is updatable if the statement against the view can be translated into the corresponding statement against the underlying table.

Let’s consider the following database tables:

Oracle Updatable View - Sample Tables

In database diagram, a car belongs to one brand while a brand has one or many cars. The relationship between brand and car is a one-to-many.

The following SQL statements create the cars and brands tables; and also insert sample data into these tables.

CREATE TABLE brands( brand_id NUMBER GENERATED BY DEFAULT AS IDENTITY, brand_name VARCHAR2(50) NOT NULL, PRIMARY KEY(brand_id) ); CREATE TABLE cars ( car_id NUMBER GENERATED BY DEFAULT AS IDENTITY, car_name VARCHAR2(255) NOT NULL, brand_id NUMBER NOT NULL, PRIMARY KEY(car_id), FOREIGN KEY(brand_id) REFERENCES brands(brand_id) ON DELETE CASCADE ); INSERT INTO brands(brand_name) VALUES('Audi'); INSERT INTO brands(brand_name) VALUES('BMW'); INSERT INTO brands(brand_name) VALUES('Ford'); INSERT INTO brands(brand_name) VALUES('Honda'); INSERT INTO brands(brand_name) VALUES('Toyota'); INSERT INTO cars (car_name,brand_id) VALUES('Audi R8 Coupe', 1); INSERT INTO cars (car_name,brand_id) VALUES('Audi Q2', 1); INSERT INTO cars (car_name,brand_id) VALUES('Audi S1', 1); INSERT INTO cars (car_name,brand_id) VALUES('BMW 2-serie Cabrio', 2); INSERT INTO cars (car_name,brand_id) VALUES('BMW i8', 2); INSERT INTO cars (car_name,brand_id) VALUES('Ford Edge', 3); INSERT INTO cars (car_name,brand_id) VALUES('Ford Mustang Fastback', 3); INSERT INTO cars (car_name,brand_id) VALUES('Honda S2000', 4); INSERT INTO cars (car_name,brand_id) VALUES('Honda Legend', 4); INSERT INTO cars (car_name,brand_id) VALUES('Toyota GT86', 5); INSERT INTO cars (car_name,brand_id) VALUES('Toyota C-HR', 5);
Code language: SQL (Structured Query Language) (sql)

Oracle updatable view example

The following statement creates a new view named cars_master:

CREATE VIEW cars_master AS SELECT car_id, car_name FROM cars;
Code language: SQL (Structured Query Language) (sql)

It’s possible to delete a row from the cars table via the cars_master view, for example:

DELETE FROM cars_master WHERE car_id = 1;
Code language: SQL (Structured Query Language) (sql)

And you can update any column values exposed to the cars_master view:

UPDATE cars_master SET car_name = 'Audi RS7 Sportback' WHERE car_id = 2;
Code language: SQL (Structured Query Language) (sql)

We could do insert and update data from the cars table via cars_master view because Oracle can translate the INSERT and UPDATE statements to the corresponding statements and execute them against the cars table.

However, insert a new row into the cars table via the cars_master view is not possible. Because the cars table has a not null column ( brand_id) without a default value.

INSERT INTO cars_master VALUES('Audi S1 Sportback');
Code language: SQL (Structured Query Language) (sql)

Oracle issued an error:

SQL Error: ORA-00947: not enough values
Code language: SQL (Structured Query Language) (sql)

Oracle updatable join view example

Let’s create a join view named all_cars based on the cars and brands tables.

CREATE VIEW all_cars AS SELECT car_id, car_name, c.brand_id, brand_name FROM cars c INNER JOIN brands b ON b.brand_id = c.brand_id;
Code language: SQL (Structured Query Language) (sql)

The following statement inserts a new row into the cars table via the call_cars view:

INSERT INTO all_cars(car_name, brand_id ) VALUES('Audi A5 Cabriolet', 1);
Code language: SQL (Structured Query Language) (sql)

A new row has been inserted into the cars table. This INSERT statement works because Oracle can decompose it to an INSERT statement against the cars table.

The following statement deletes all Honda cars from the cars table via the all_cars view:

DELETE FROM all_cars WHERE brand_name = 'Honda'; </code>
Code language: HTML, XML (xml)

2 rows deleted.

Oracle has some rules and restrictions that apply to updatable join views. One of them is the concept of key-preserved tables.

A key-preserved table is a base table with a one-to-one row relationship with the rows in the view, via either the primary key or a unique key. In the example above, the cars table is a key-preserved table.

Here are some examples of updatable join view restrictions:

  • The SQL statement e.g., INSERT, UPDATE, and DELETE, is only allowed to modify data from a single base table.
  • For an INSERT statement, all columns listed in the INTO clause must belong to a key-preserved table.
  • For an UPDATE statement, all columns in the SET clause must belong to a key-preserved table.
  • For a DELETE statement, if the join results in more than one key-preserved table, the Oracle deletes from the first table in the FROM clause.

Besides these restrictions, Oracle also requires that the defining-query does not contain any of the following elements:

Find updatable columns of a join view

To find which column can be updated, inserted, or deleted, you use the user_updatable_columns view. The following example shows which column of the all_cars view is updatable, insertable, and deletable:

SELECT * FROM USER_UPDATABLE_COLUMNS WHERE TABLE_NAME = 'ALL_CARS';
Code language: SQL (Structured Query Language) (sql)
Oracle Updatable Join view

In this tutorial, you have learned about the Oracle updatable view and how to update underlying base tables through it.

Was this tutorial helpful?