Home » Oracle SQL » Oracle Updatable View With Examples

Oracle Updatable View With Examples

We previously learned about the concept of Views in Oracle and how we can create, update and delete them using simple queries.

In practice, A view is just like a table from which we can fetch data using SQL queries. But its not always possible to update the data of underlying base table by updating the view. We can only update data in underlying base table via view when there is one to one relationship between the view and underlying table.

We cannot update a view if it contains any of the following:

Aggregate functions like SUM, MAX, MIN etc.
GROUP BY clause
DISTINCT clause
HAVING
Set Operators like UNION, INTERSECT etc.
Subquery in SELECT
START WITH or CONNECT BY clause
Certain Joins

Let’s create two tables named Students and Teachers and insert dummy data for understanding this through examples:

CREATE TABLE teachers
( teacher_id NUMBER(10),
  teacher_name VARCHAR2(50),
  courses VARCHAR2(50),
  CONSTRAINT teacher_pk PRIMARY KEY (teacher_id)
);

CREATE TABLE students
( student_id NUMBER(10),
  student_name VARCHAR2(30),
  teacher_id NUMBER(10),
    PRIMARY KEY (student_id),
    FOREIGN KEY (teacher_id)
    REFERENCES teachers(teacher_id)
);

INSERT INTO TEACHERS 
VALUES ( 10, 'Melanie', 'Physics');
INSERT INTO TEACHERS 
VALUES ( 20, 'Dwayne', 'Biology');

INSERT INTO students 
VALUES ( 1, 'Max', 10);
INSERT INTO students 
VALUES ( 2, 'Kim', 10);
INSERT INTO students 
VALUES ( 3, 'Mia', 20);
INSERT INTO students 
VALUES ( 4, 'Bob', 20);
INSERT INTO students 
VALUES ( 5, 'Ash', 20);

Result:

Oracle Updatable View example

Let’s create a simple view that’s based on just one table, students:

CREATE VIEW students_view AS 
SELECT
    student_id,
    student_name
FROM
    students;

Let’s see how we can delete rows from underlying table, ‘students’ via this view:

DELETE
FROM
    students_view
WHERE
    student_id = 5;

The student with student_id = 5 has been deleted from underlying ‘students’ table.

Similarly, we can update values in ‘Students’ table by using UPDATE statement on view:

UPDATE
    students_view
SET
    student_name = 'Misty'
WHERE
    student_id = 2;

This query updates the student name in Student table where student_id =2 and changes name of student from ‘Kim’ to ‘Misty’.

Let’s try to insert a new student in ‘ Students’ table by using INSERT statement on ‘students_view’

INSERT INTO students_view
VALUES('Rock');

This time Oracle returns an error saying: SQL Error: ORA-00947: not enough values

This is because, we this query violates the ‘Primary key’ constraint on students_id column as we did not enter any values for it and it can’t be kept NULL.

While using INSERT, UPDATE or DELETE statements on view its important to keep in mind that the operation we perform on view should not violate any existing constraints or rules of underlying base tables on which these views are based.

Oracle Updatable View with Join example

This time we will create a view which is based on two table and contains a join condition:

CREATE VIEW join_view AS 
SELECT
    student_id,
    student_name,
    t.teacher_id,
    t.teacher_name,
    t.courses
FROM
    students s
INNER JOIN teachers t ON
    s.teacher_id = t.teacher_id;

Let’s use SELECT query to see how it looks:

SELECT * FROM join_view;

Result:

Let’s insert one new row into ‘Students’ table using this view:

INSERT INTO join_view(student_id, student_name )
VALUES(6, 'Lily');

The new row has been inserted into the students table via this view, because it does not violate any rules of that underlying table.

Similarly, lets try to update rows in Teachers table via this view:

UPDATE
    join_view
SET
    courses = 'Chemistry'
WHERE
    teacher_id = 20;

This time Oracle returns an error:

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

There are some restrictions when we try to use INSERT, UPDATE or DELETE operations on views to make changes to underlying base tables.

A table which has one-to-one relationship with the rows that exist in view via a constraint like Primary or Unique key is known as Key-preserved table. Teachers table in this case is the key-preserved table.

Let’s look at some restrictions we need to keep in mind while using INSERT, UPDATE or DELETE statements on views:

INSERT, UPDATE or DELETE statements applied on View can only make changes to one underlying base table.
For INSERT – all Columns listed in the INTO clause must be from a key-preserved table.
For UPDATE – all Columns listed in SET clause must be from a key-preserved table.
For DELETE – Due to Join when the join-result contains more than one key-preserved table, the Oracle deletes from only the first table in the FROM clause.

For finding the updatable columns of a Join view we can use this query:

We use the user_updatable_columns view to find out which columns in the join view can be updated, inserted or deleted.

Let’s see the following query:

SELECT
    *
FROM
    USER_UPDATABLE_COLUMNS
WHERE
    TABLE_NAME = 'join_view';

Result:

As we can see, we can’t update, insert or delete values from Teachers table.

In this tutorial, we understood the concept behind how to use updatable views to update values in base table and the concept of key-preserved table which prevents us from updating values in underlying table using views.

<< Previous
Oracle VIEW

Next >>
INLINE VIEW