Have you ever wanted a joined table view to be updateable but all you get is read-only? This tip from Michael V.
Mannino's Database Application Development and Design (McGraw-Hill) lays down some rules to follow to make sure your multiple-table views are updateable:
It may be surprising but some multiple-table views are also updateable. A multiple-table view may correspond in a one-to-one manner with rows from more than one table if the view contains the primary key of each table. Because multiple-table views are more complex than single-table views, there is not wide agreement on updateability rules for multiple-table views. Some DBMSs may not support updateability for any multiple-table views. Other systems support updateability for a large number of multiple-table views. In the section below, the updateability rules of Microsoft Access are described as they support a wide range of multiple-table views. The rules for updateable join views in Oracle 8 are similar to Microsoft Access although Oracle is somewhat more restrictive on the allowable manipulation operations.
In Access, for example, multiple-table queries known as 1-M queries are updateable. A 1-M query involves two or more tables with one table playing the role of the 1 table and another table playing the role of the M (many) table. To make a 1-M query updateable, follow these rules:
- The query includes the primary key of the M table.
- The query contains all required fields (NOT NULL) of the M table.
- The query does not include GROUP BY or DISTINCT.
- The join field of the 1 table should be unique (either a primary key or a unique specification).
- The query contains the foreign key column(s) of the M table.
- The query includes the primary key and required fields of the 1 table as if the view supports insert operations on the 1 table. Update operations are supported n the 1 table even if the primary key is omitted in the view.
Using these rules, Course_Offering_View1 (Example 1, below) and Faculty_Offering_View1 (Example 3) are updateable. Course_Offering_View2 (Example 2) is not updateable because Offering.CourseNo (the foreign key of the M table) is missing. In the SELECT statements, the join operator style (INNER JOIN keywords) is used because Microsoft Access requires it for updateable 1-M queries.
Note: For Oracle and SQL2 the CREATE VIEW statement would be used with the SELECT in the below examples.
The following examples are based on these table creation statements.
CREATE TABLE Course ( CourseNo CHAR(6), CrsDesc VARCHAR(250), CrsUnits SMALLINT, CONSTRAINT PKCourse PRIMARY KEY(CourseNo), CONSTRAINT UniqueCrsDesc UNIQUE (CrsDesc))
CREATE TABLE Faculty ( FacSSN CHAR(11) NOT NULL, FacFirstName VARCHAR(50) NOT NULL, FacLastName VARCHAR(50) NOT NULL, FacCity VARCHAR(50) NOT NULL, FacState CHAR(2) NOT NULL, FacZipCode CHAR(10) NOT NULL, FacHireDate DATE, FacDept CHAR(6), FacRank CHAR(4), FacSalary DECIMAL(10,2), FacSupervisor CHAR(11), CONSTRAINT PKFaculty PRIMARY KEY (FacSSN), CONSTRAINT FKFacSupervisor FOREIGN KEY (FacSupervisor) REFERENCES Faculty)
CREATE TABLE Offering ( OfferNo CHAR(4), CourseNo CHAR(5), OffTerm VARCHAR(10), OffYear CHAR(4), OffLocation CHAR(6), OffTime TIME, FacSSN CHAR(11), OffDays VARCHAR(5), CONSTRAINT PKOffering PRIMARY KEY (OfferNo))
Example 1. This is an 1-M updateable query with a join between the Course and the Offering tables.
Course_Offering_View1: SELECT Course.CourseNo, CrsDesc, CrsUnits, Offering.OfferNo, OffTerm, OffYear, Offering.CourseNo, OffLocation, OffTime, FacSSN, OffDays FROM Course INNER JOIN Offering ON Course.CourseNo = Offering.CourseNo
Example 2. This query is read-only because it does not contain Offering.CourseNo.
Course_Offering_View2: SELECT CrsDesc, CrsUnits, Offering.OfferNo, Course.CourseNo, OffTerm, OffYear, OffLocation, OffTime, FacSSN, OffDays FROM Course INNER JOIN Offering ON Course.CourseNo = Offering.CourseNo
Example 3. This is an updateable 1-M query with a join between the Faculty and the Offering tables.
Faculty_Offering_View1: SELECT Offering.OfferNo, Offering.FacSSN, CourseNo, OffTerm, OffYear, OffLocation, OffTime, OffDays, FacFirstName, FacLastName, FacDept FROM Faculty INNER JOIN Offering ON Faculty.FacSSN = Offering.FacSSN
For More Information
- Purchase Database Application Development and Design here.
- What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Database Design Web Links: tips, tutorials, scripts, and more.
- Have a database design tip to offer your fellow developers and DBA's? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical database design questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our database design gurus are waiting to answer your toughest questions.