What Is the Effect on the Base Table When the Data in a View Is Changed?
Many modern database management systems, including Oracle, IBM’s DB2 and Microsoft’s SQL Server, let you use database structures called views as an alternative to data tables. A view is a “virtual table,” appearing as a set of columns and rows and having a table as a source. As with tables, you can change data in views by using appropriate structured query language statements. Updating data has some restrictions that depend on the tables underlying the view.
Other People Are Reading
-
Views
-
Databases offer views as a convenient, secure way to represent data. You make a view using the database’s “CREATE VIEW” statement, providing a “SELECT” statement that specifies one or more tables along with conditions. The following statement, for example, creates a view based on a customer table:
CREATE VIEW indiana_customers
AS SELECT customer_id, name, address FROM customers
WHERE state = ‘IN’;This view shows only Indiana customers. A view may have the same number of fields as the parent table or it may omit fields. For example, you might give someone access to a view based on a company’s employee table, but leave out salary and other confidential information.
Single Table Views
-
You can change the data in a view based on a single table with few restrictions. “UPDATE,” “INSERT” and “DELETE” SQL statements treat views the same as tables; changes made to the view data flow back to the underlying table. However, you have to be careful about field rules such those not allowing null values; if the table has fields with rules but the view does not, an INSERT statement may fail because the view cannot pass values to the table for those fields it does not include.
-
Multiple Table Views
-
A view can incorporate data from more than one table; the view’s SELECT statement may have a JOIN clause connecting two tables together. In this case the view may have fields from either table. You can change the data in a multiple-table view, though you have to observe a few rules. You may change information on only one of the underlying tables, and that table must have a key field present in the view. The brand of database software may have its own rules for updating these types of views.
Non-Updatable Views
-
Some views do not allow updates. For example, Oracle has a “WITH READ ONLY” option that prevents a view from changing table data. You cannot update a view that specifies the “DISTINCT” keyword or “GROUP BY” clauses in its SELECT statements, as its records consist of aggregates of underlying table records. Though you can work around this with database triggers such as “INSTEAD OF,” this approach adds complexity to data updates.
-
References
- Photo Credit Thinkstock Images/Comstock/Getty Images
