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.