How To Create Updateable Views

It has been said that perception is more important than reality. I agree, especially when such perception is implemented as a view in a relational database. When creating a view, it is good to keep two things in mind.

First, consider that a view may be updateable or read-only. An updateable view allows the user to update rows in the table through the view, but also to insert new rows and delete rows. Read-only views may only be used when retrieving data. Both types of view are superb, and each type has its uses.

One strong advantage of updateable views is that they decrease the effort required to maintain applications. For example, a view that mimics the format (record layout) of a table that is no longer in use permits old programs to run without having to be modified to read the table that replaced the former table.

If you need for a view to be updateable, you have to follow a few sensible rules:

1. The view must be built over one table–no more and no less.

2. Each row of the view must map back to exactly one row of the underlying table.

3. The view may include calculated columns, but you cannot update such columns.

4. The view may not contain aggregate processing (i.e. GROUP BY, HAVING, MIN, MAX, SUM, etc.).

5. In order to insert new rows through the view, all columns of the table that are not also in the view must be null-capable. Obviously this requirement means that the view must include the primary key in order to support inserts.

In short . . .

No joins.

No SELECT DISTINCT.

No UNION, no EXCEPT, no INTERSECT.

No aggregate or summary columns.

There is a way around these limitations. To update through a read-only view, use an INSTEAD OF trigger. See Michael Sansoterra’s excellent article, Perform the Impossible: Update a Multi-Table View using INSTEAD OF Triggers for more information.

Second, you must also decide whether or not inserts and updates should be allowed to violate row-selection criteria. Sometimes they should. Sometimes they shouldn’t.

For example, assume a view that shows manufacturing orders that are in progress. The view does not include orders that have not been started, nor orders that have been completed. You may want to allow a user to update the order status to “complete” to remove the order from the view, but of course, not from the table.

On the other hand, suppose purchasing programs use a view that selects purchased (as opposed to manufactured) items only. Those programs could be prevented from updating the type column, which would change a purchased part to a manufactured part.

To force all rows that are inserted or updated through the view to conform to the row selection criteria of the view, use the WITH CHECK OPTION clause.

Let me illustrate.

Suppose you have a table of employee data.

create table Employees
  ( ClockNumber for column Clock
                dec (5)       not null,
    Name        varchar(20),
    Type        char(1),
    Department  char(2),
    NationalID  char(9),
    MaritalStatus for column Marital
                char(1),
    Exemptions  dec(3),
    TelephoneNumber for column Telephone
                char(6),
    primary key (ClockNumber))

insert into employees values
( 101, 'Barney Fife',      'S', '07', 111, 'S', 0, 'BR-549'),
( 102, 'Luther Heggs',     'S', '02', 222, 'S', 0, 'AA-123'),
( 103, 'Theodore Ogilvie', 'S', '03', 333, 'M', 0, null),
( 104, 'Jesse W. Haywood', 'H', '03', 444, 'S', 0, '56565'),
( 105, 'Lester Calhoun',   'H', '02', 555, 'M', 0, null),
( 106, 'Roy Fleming',      'H', '02', 666, 'S', 0, null)

Suppose you want certain users, who use a query tool on a PC, to access the contact information of hourly employees. You could give them access to an hourly contacts view.

create view HourlyContacts
   for system name HContacts
   (ClockNumber, Name, TelephoneNumber)
 as (select ClockNumber, Name, TelephoneNumber
       from Employees
      where Type = 'H')

Suppose someone inserts a new employee using that view.

insert into HourlyContacts values
   (107, 'Henry Limpet', '70707')

DB2 for i happily responds with message SQL7856 (1 rows inserted in HCONTACTS in MYLIB.)

However, querying the hourly contacts view does not show the new employee.

select * from HourlyContacts

CLOCKNUMBER  NAME               TELEPHONENUMBER
      104    Jesse W. Haywood        56565
      105    Lester Calhoun          -
      106    Roy Fleming             -

However, suppose the view were created with the check option.

create view HourlyContacts
   for system name HContacts
   (ClockNumber, Name, TelephoneNumber)
 as (select ClockNumber, Name, TelephoneNumber
       from Employees
      where Type = 'H')
with check option

The same insert fails with message SQL0161 (INSERT or UPDATE not allowed because a resulting row does not satisfy view definition HCONTACTS in MYLIB.).

Let’s try one more time. Add the type column to the view.

create view HourlyContacts
   for system name HContacts
   (ClockNumber, Name, Type, TelephoneNumber)
 as (select ClockNumber, Name, Type, TelephoneNumber
       from Employees
      where Type = 'H')
with check option

Insert a salaried employee.

insert into HourlyContacts values
   (107, 'Henry Limpet', 'S', '70707')

Again DB2 heartlessly responds with SQL0161.

But you can insert hourly employees.

insert into HourlyContacts values
   (107, 'Henry Limpet', 'H', '70707')

DB2 responds with message SQL7956 (1 rows inserted in HCONTACTS in MYLIB.)

Here’s the data as seen through the view.

select * from HourlyContacts 

CLOCKNUMBER  NAME                  TYPE  TELEPHONENUMBER
      104    Jesse W. Haywood       H        56565
      105    Lester Calhoun         H        -
      106    Roy Fleming            H        -
      107    Henry Limpet           H        70707

Keep in mind that I am only talking about inserting and updating through the view. WITH CHECK OPTION has nothing to do with inserting and updating through non-related views and the underlying table itself.

Both updateable and read-only views have their places. It’s worth spending a little extra time before creating a view to decide whether or not the view should be updateable, and whether or not you should allow inserts and updates that don’t conform to the row selection of the view.

RELATED STORY

Perform the Impossible: Update a Multi-Table View using INSTEAD OF Triggers