Multiple-table updateable views

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

    Requires Free Membership to View

    Login

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:

  1. The query includes the primary key of the M table.
  2. The query contains all required fields (NOT NULL) of the M table.
  3. The query does not include GROUP BY or DISTINCT.
  4. The join field of the 1 table should be unique (either a primary key or a unique specification).
  5. The query contains the foreign key column(s) of the M table.
  6. 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.

Course table:

CREATE TABLE Course
	(	CourseNo CHAR(6),
		CrsDesc	VARCHAR(250),
			CrsUnits SMALLINT,
CONSTRAINT PKCourse PRIMARY KEY(CourseNo),
CONSTRAINT UniqueCrsDesc UNIQUE (CrsDesc))

Faculty table:

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)

Offering table:

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


This was first published in May 2001

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top