Skip to content Skip to main navigation Skip to footer

DML Operations and Views

Objectives

This lesson covers the following objectives:

  • Write and execute a query that performs DML operations on a simple view
  • Name the conditions that restrict your ability to modify a view using DML operations
  • Write and execute a query using the WITH CHECK OPTION clause
  • Explain the use of WITH CHECK OPTION as it applies to integrity constraints and data validation
  • Apply the WITH READ ONLY option to a view to restrict DML operations

Purpose

  • As you learned in the last lesson, views simplify user access to data contained in one or more tables in the database.
  • However, views also allow users to make changes to the underlying tables.
  • As the DBA and the person whose job it is to maintain the integrity of the database, you may want to put constraints on certain views of data.
  • In this lesson, you will learn how to allow data access and at the same time ensure data security.

DML Statements and Views

  • The DML operations INSERT, UPDATE, and DELETE can be performed on simple views.
  • These operations can be used to change the data in the underlying base tables.
  • If you create a view that allows users to view restricted information using the WHERE clause, users can still perform DML operations on all columns of the view.
  • For example, the view shown on the right was created for the managers of department 50 from the employees database.
  • The intent of this view is to allow managers of department 50 to see information about their employees.
CREATE VIEW view_dept50
AS SELECT department_id, employee_id,first_name, last_name, salary
FROM copy_employees
WHERE department_id = 50;
SELECT * FROM view_dept50;
DML Statements and Views

Controlling Views

  • Using the view as stated, it is possible to INSERT, UPDATE, and DELETE information for all rows in the view, even if this results in a row no longer being part of the view.
  • This may not be what the DBA intended when the view was created.
  • To control data access, two options can be added to the CREATE VIEW statement:
    • WITH CHECK OPTION
    • WITH READ ONLY

Views with CHECK Option

  • The view is defined without the WITH CHECK OPTION.
CREATE VIEW view_dept50
AS SELECT department_id, employee_id, first_name, last_name, salary
FROM copy_employees
WHERE department_id = 50;
  • Using the view, employee_id 124 has his department changed to dept_id 90.
UPDATE view_dept50
SET department_id = 90
WHERE employee_id = 124;

1 Row(s) Updated.

  • The update succeeds, even though this employee is now not part of the view.
  • The WITH CHECK OPTION ensures that DML operations performed on the view stay within the domain of the view.
  • Any attempt to change the department number for any row in the view fails because it violates the WITH CHECK OPTION constraint.
  • Notice in the example below that the WITH CHECK OPTION CONSTRAINT was given the name view_dept50_check.
CREATE OR REPLACE VIEW view_dept50
AS SELECT department_id, employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 50
WITH CHECK OPTION CONSTRAINT view_dept50_check;
  • Now, if we attempt to modify a row in the view that would take it outside the domain of the view, an error is returned.
UPDATE view_dept50
SET department_id = 90
WHERE employee_id = 124;

ORA-01402: view WITH CHECK OPTION where-clause violation

Views with READ ONLY

  • The WITH READ ONLY option ensures that no DML operations occur through the view.
  • Any attempt to execute an INSERT, UPDATE, or DELETE statement will result in an Oracle server error.
CREATE OR REPLACE VIEW view_dept50
AS SELECT department_id, employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 50
WITH READ ONLY;

DML Restrictions

  • Simple views and complex views differ in their ability to allow DML operations through a view.
  • For simple views, DML operations can be performed through the view.
  • For complex views, DML operations are not always allowed.
  • The following three rules must be considered when performing DML operations on views.
  • You cannot remove a row from an underlying base table if the view contains any of the following:
    • Group functions
    • A GROUP BY clause
    • The DISTINCT keyword
    • The pseudocolumn ROWNUM Keyword
  • You cannot modify data through a view if the view contains:
    • Group functions
    • A GROUP BY clause
    • The DISTINCT keyword
    • The pseudocolumn ROWNUM keyword
    • Columns defined by expressions
  • You cannot add data through a view if the view:
    • includes group functions
    • includes a GROUP BY clause
    • includes the DISTINCT keyword
    • includes the pseudocolumn ROWNUM keyword
    • includes columns defined by expressions
    • does not include NOT NULL columns in the base tables

Terminology

Key terms used in this lesson included:

  • WITH CHECK OPTION
  • WITH READ ONLY

Summary

In this lesson, you should have learned how to:

  • Write and execute a query that performs DML operations on a simple view
  • Name the conditions that restrict your ability to modify a view using DML operations
  • Write and execute a query using the WITH CHECK OPTION clause
  • Explain the use of WITH CHECK OPTION as it applies to integrity constraints and data validation
  • Apply the WITH READ ONLY option to a view to restrict DML operations

Was This Article Helpful?

0
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *