Ограничения целостности. Представления данных

Более сложные правила целостности

Заявляемые ограничения целостности в Oracle приспособлены для записи важных, но простейших видов дополнительных ограничений на помещаемые в базу значения данных. Более сложные правила (по более современной терминологии "бизнес"-правила) могут быть заданы с использованием:

• триггерных процедур (выполняются СУБД Oracle);
• аппарата транзакций и блокировок (выполняется в СУБД Oracle);
• логики прикладной программы (воплощается клиентским приложением).

Порядок в указанном перечне соответствует убыванию гарантированности соблюдения правил целостности в фактически оказавшихся в базе данных (соответственно — увеличению риска получить в БД некорректные с прикладной точки зрения данные) по отношению к заявляемым в схеме ограничениям.

Представления данных, или же виртуальные таблицы (views)

Буквальным переводом английского термина view в базах данных является "вид" [на данные]. Термин является сокращением от бытовавшего когда-то более точного названия data view. Содержательно правильным переводом могут быть термины "виртуальная", "выводимая", "производная" или же "синтезированная" таблица. Буквальный перевод в русском языке не прижился, а содержательно более ему предпочтительные оказались чересчур громоздкими. По этим и другим причинам у нас широко распространился "перевод" представление (данных). Все используемые и не вошедшие в оборот русские термины чем-то неудачны. Справедливости ради можно отметить, что и выбор оригинального термина не бесспорен. Предлагается, например, вместо view использовать "более правильный" термин derived table.

Название "виртуальная таблица", принятое здесь наравне с "представлением", перекликается с названием "виртуальный столбец", официально появившимся в Oracle версии 11.

Понятие view попало в SQL из реляционной теории, но в выхолощеном виде, потеряв многие интересные качества. Там радикального отличия "основных отношений" от "производных" нет, и по сути все отношения являются как бы "представлениями" ("видимостями") данных, допускающими внесение через себя изменений в БД, если только это теоретически позволительно.

В SQL термин используется для обозначения запроса SELECT, текст и разобранная структура которого хранятся в БД под определенным именем. Формальное основание такому хранению дает то обстоятельство, что любой запрос в SQL возвращает набор данных, структурированный в виде столбцов и строк, равно как в таблице, извлеченной из БД. Ничто не мешает взять этот набор данных в качестве источника для другого запроса. Если в запросе указать в качестве источника данных не реальную таблицу, а виртуальную ("представление"), Oracle обнаружит подмену, вычислит сходу запрос для нее и полученный результат предъявит для обработки основному запросу. Стоит только добавить, что это никогда не нарушаемая логика обработки, в то время как технически Oracle вполне может поступать иначе. Например, при определенных обстоятельствах Oracle может "растворить" текст view в основном запросе и вычислять основной запрос сразу и без предварительной фазы вычисления подзапроса-view или же вести вычисление и основного запроса, и данных подзапроса-view параллельно и так далее.

Таким образом, для запросов SELECT, поступающих из приложения, нет никакой содержательной разницы, скрывается ли под именем источника данных виртуальная или реальная таблица, и тем самым выдержана преемственность реляционной модели. Разница может касаться только затрат ресурсов СУБД на выполнение.

Просто таблицы можно в противовес view уточнять словами "реальные", "основные", "базовые" (что то же самое), "исходные". Часто возникает соблазн назвать их "хранимыми", противопоставляя "вычислимым" view, но Oracle (и не только) дает пример "исходных" таблиц, не хранимых в БД: это так называемые X$-таблицы, служащие администрированию.

Виртуальные таблицы позволяют решать в базе данных по крайней мере три важные задачи:

• удобства предоставления данных пользователю и упрощения вида запросов;
• ограничения доступа к данным;
• приспособления к изменениям в модели предметной области путем учета этих изменений без перекройки накопленных в БД данных.

Примеры определений представлений данных

Ниже приводятся некоторые примеры создания и удаления виртуальных таблиц. Таблицы EMP и DEPT являются "реальными" (иначе — основными, или базовыми), а таблицы JOBS, COMMISSIONERS и EMPLOYEES — виртуальными, то есть представлениями данных.

CREATE VIEW jobs AS SELECT DISTINCT job FROM emp;
CREATE VIEW commissioners 
AS 
   SELECT * 
   FROM   emp
   WHERE  comm IS NOT NULL
;
CREATE VIEW employees ( name, place ) 
AS 
   SELECT ename, loc
   FROM   emp, dept
   WHERE  emp.deptno = dept.deptno
;
DROP VIEW employees;

Обновление БД через представления данных

Техника представлений позволяет дать программисту удобный взгляд на данные. Так как с точки зрения выборки данных эти виртуальные таблицы ничем не отличаются от реальных, со временем неизбежно возникает желание применять к ним не только SELECT, но и операторы DML. Смысл применения к представлению данных операторов INSERT, UPDATE и DELETE логично понимать как попытку внести изменения в основные таблицы таким образом, чтобы создалось впечатление изменения таблицы виртуальной. То есть, строго говоря, речь идет не об "обновлении представлений данных" операторами DML, а об "обновлении БД" этими операторами "через представления".

Осуществить такие изменения в БД в каждом конкретном случае удается не всегда, и по разным причинам: отчасти по объективным, отчасти по субъективным.

Oracle делит все представления данных на две категории: обновляемые (указанным выше образом) и необновляемые. Простейшим случаем обновляемого является представление, построенное запросом к единственной основной таблице. С версии 8 иногда стало возможно обновление представлений, построенных на основе запроса к более чем одной основной таблице. (При этом формальная обновляемость не гарантирует фактическое выполнение конкретной операции DML применительно к view: иногда оно может вступить в противоречие ограничениям целостности, связаным с таблицей. Примером того, как можно попытаться избежать подобной неопределенности, является включение в запрос для view поля первичного ключа — разумеется, когда таковой у основной таблицы имеется.)

В любом случае Oracle запрещает обновление данных, если в определении представления предложение SELECT содержит обобщение в том или ином виде, как то:

• DISTINCT;
• агрегатные (обобщающие) и аналитические функции;
• GROUP BY;
• множественные операции.

Если таблица причислена к обновляемым, некоторые ее столбцы могут оказаться закрытыми для обновления. Так, чтобы столбец допускал изменения, требуется, чтобы во фразе SELECT подлежащего запроса он:

• не претерпевал преобразований в виде выражений (функций, скалярных подзапросов и так далее);
• не был виртуальным столбцом или "псевдостолбцом";
• если подлежащий запрос — соединение, операция DML должна прилагаться к унаследованному в определении подлежащего предложения SELECT первичному или уникальному ключу.

В любом случае, через представление позволено изменять данные не более чем одной основной таблицы.

Более точный список правил предъявления операции DML к представлениям данных приведен в общем виде в документации по Oracle, а применительно к конкретным представлениям список обновляемых столбцов можно узнать из таблицы USER_UPDATABLE_COLUMNS словаря-справочника.

Из приводившихся выше определений:

• COMMISSIONERS допускает употребление себя в операторах DML без ограничений;
• JOBS не допускает употребление в операторах DML;
• EMPLOYEES допускает употребление в операторах DML, подразумевающих действительным объектом изменения основную таблицу EMP (изменить столбец PLACE, например, будет нельзя).

Радикально решить все проблемы обновления данных через представления способна триггерная процедура типа INSTEAD OF, с помощью которой разработчик получает шанс самостоятельно запрограммировать необходимые изменения в БД, отражающие, по его мнению, смысл применения INSERT, UPDATE или DELETE к любому представлению.