Oracle Updatable View

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

Introduction to Oracle Updatable 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:

In a database diagram, a car belongs to one brand while a brand has one or many cars. The relationship between brand and car is 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)

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 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 valuesCode 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 language: JavaScript (javascript)

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:

• Aggregate functions e.g., AVG, COUNT, MAX, MIN, and SUM.
• DISTINCT operator.
• GROUP BY clause.
• HAVING clause.
• Set operators e.g., UNION, UNION ALL, INTERSECT, and MINUS.
• START WITH or CONNECT BY clause
• ROWNUM pseudo-column

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)

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