Многотабличные обновляемые представления

Мишель В. Маннино

(Multiple-table updateable views, by Michael V. Mannino)
Источник: http://searchdatabase.techtarget.com/tip/0,289483,sid13_gci551163,00.html
Распространено 9 Мая 2001.

Хотелось ли вам когда-нибудь, чтобы представление, основанное на соединении таблиц, было обновляемым, но все что вам удавалось получить - это представление только для чтения? Мишель В. Маннино в книге "Database Application Development and Design" ("Руководство по проектированию и разработке приложений для баз данных"), изд. McGraw-Hill приводит несколько правил, следуя которым можно гарантировать, что ваши представления из нескольких таблицах, будут обновляемыми.

Может показаться удивительным, но некоторые представления, основанные на нескольких таблицах, сразу же являются обновляемыми. Это в происходит, если в представлении содержится первичный ключ каждой таблицы, тогда строка многотабличного представления однозначно соответствует строкам более чем одной таблицы. В общем случае, поскольку многотабличные представления являются более сложными объектами, чем представления, основанные на одной таблице, то для таких представлений не существует общего соглашения о правилах обновления.

Некоторые СУБД вообще не поддерживают возможность обновления для многотабличных представлений. Другие системы поддерживают возможность обновления для значительной части многотабличных представлений. В приведенном ниже разделе описаны правила обновления таких представлений как в Oracle 8, так и в Microsoft Access, поскольку там поддерживается обновление для широкого диапазона многотабличных представлений.

Например, запросы к нескольким таблицам, известные как (1-M)-запросы являются обновляемыми. (1-M)-запрос включает две или несколько таблиц, при этом одна таблица играет роль главной (1) таблицы, а другая выступает в роли подчиненной (M) таблицы. Чтобы (1-M)-запрос был обновляемым, необходимо следовать следующим правилам:

Запрос должен включать первичный ключ подчиненной таблицы.

Запрос должен содержать все обязательные поля (NOT NULL) подчиненной таблицы.

Запрос не должен включать GROUP BY или DISTINCT.

Поле соединения главной таблицы должно быть уникальным (либо первичный ключ, либо ограничение уникальности).

Запрос должен содержать столбец внешнего ключа подчиненной таблицы.

Запрос должен включать первичный ключ и обязательные поля главной таблицы, если необходимо, чтобы представление поддерживало операции вставки для главной таблицы. Операции обновления для главной таблицы будут поддерживаться, даже если первичный ключ в представлении опущен.

Использующие эти правила представления Course_Offering_View1 (Пример 1, см ниже) и Faculty_Offering_View1 (Пример 3) являются обновляемыми. Представление Course_Offering_View2 (Пример 2) не является обновляемым, поскольку пропущено поле Offering.CourseNo (внешний ключ главной таблицы). В предложении SELECT используется определенный стиль оператора соединения (ключевые слова INNER JOIN), поскольку Microsoft Access требует этого для обновляемых (1-M)-запросов.

Примечание: Для Oracle и SQL2 вместе с оператором SELECT, приведенным в следующих ниже примерах, должно быть использовано предложение CREATE VIEW.

Следующие примеры основаны на таблицах, для создания которых нужно выполнить операторы, приведенные ниже.

Таблица Course:
 
CREATE TABLE Course
 ( CourseNo CHAR(6),
 CrsDesc VARCHAR(250),
 CrsUnits SMALLINT,
CONSTRAINT PKCourse PRIMARY KEY(CourseNo),
CONSTRAINT UniqueCrsDesc UNIQUE (CrsDesc))
 
Таблица Faculty:
 
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:
 
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)) 
 

Пример 1. Это обновляемый (1-M)-запрос с соединением таблиц Course и Offering.

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 
 

Пример 2. Это запрос только для чтения, потому что он не содержит поля 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
 

Пример 3. Это обновляемый (1-M)-запрос с соединением таблиц Faculty и Offering.

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