• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • How To Create Updateable Views

    July 7, 2015 Ted Holt

    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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    ARCAD Software

    Embrace VS Code for IBM i Development

    The IBM i development landscape is evolving with modern tools that enhance efficiency and collaboration. Ready to make the move to VS Code for IBM i?

    Join us for this webinar where we’ll showcase how VS Code can serve as a powerful editor for native IBM i code and explore the essential extensions that make it possible.

    In this session, you’ll discover:

    • How ARCAD’s integration with VS Code provides deep metadata insights, allowing developers to assess the impact of their changes upfront.
    • The role of Git in enabling seamless collaboration between developers using tools like SEU, RDi, and VS Code.
    • Powerful extensions for code quality, security, impact analysis, smart build, and automated RPG conversion to Free Form.
    • How non-IBM i developers can now contribute to IBM i projects without prior knowledge of its specifics, while ensuring full control over their changes.

    The future of IBM i development is here. Let ARCAD be your guide!

    Watch Now

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Fresche Legacy:  Optimize IBM i apps; Improve business processes; Deliver modernization success
    SystemObjects:  Send SMS, email & push messages from your IBM i applications!
    Four Hundred Monitor Calendar:  Latest info on national conferences, local events, & Webinars

    cozTools Brings Subscription Pricing To Software Licensing Manufacturing Automation At A ‘Crossroads’ For IBM i Shops

    Leave a Reply Cancel reply

Volume 15, Number 13 -- July 7, 2015
THIS ISSUE SPONSORED BY:

WorksRight Software
Robot
Fresche Legacy

Table of Contents

  • Looking For Stuff With iSphere
  • How To Create Updateable Views
  • Security Architecture For Business Applications

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle