This preview shows page 1. Sign up to view the full content.
Unformatted text preview: lors;
ActiveSailors; 63 Updatable Views 64 Updatable Views in MySQL SQL-92: Must be defined on a single table using only
SQLselection and projection and not using DISTINCT. Cannot include:
• aggregate functions SQL:1999: May involve multiple tables in SQL:1999 if
each view field is from exactly one underlying base
table and that table’s PK is included in view; not
restricted to selection and project, but cannot insert
into views that use union, intersection, or set
difference. • DISTINCT
• GROUP BY
• subquery in select list
• certain joins Why have these restrictions?
65 66 Updatable Views (continued) Updatable Views (continued) In addition, to be insertable:
insertable: Certain multiple-table views are updatable:
multiple- • must contain no duplicate view column names • must user inner join (not outer join or union) • must contain all columns in base table with no
default value • must result in update to a single table in view
defn. (so SET clause must name only columns
from one table in view) • must contain only simple column references, not
derived columns May...
View Full Document
- Fall '12