DeepEdit!

Рассмотрим форму счёта в учебном примере. Это типовая форма и её заполнение регламентировано оперативным учетом и правилами документооборота в организации. 

В форме есть поля, обязательные для заполнения, а есть поля, которые допускается оставлять пустыми. Например. Поля "Счёт", "Покупатель" должны быть обязательно заполненными. Поле "Адрес доставки" в нашем случае заполнено, но если услуги доставки не требуются, то продавец может оставить поле незаполненным. 

Тоже можно сказать и про позиции с товарами. Например. Продавец должен обязательно указать наименование товара, количество, цену и налоговую ставку. Эти значения будут использованы для расчёта стоимости товара, суммы налога и общей суммы по строке. Скорее всего, нельзя указывать отрицательные значения для количества и цены.

Столь очевидные правила заполнения программист может сформулировать самостоятельно. Но есть более специфичные. Как правило, их выдвигают представители заказчика или конечные пользователи. Например, бухгалтерия выдвинула следующие требования по заполнению табличной части счёта: 

Нумерация позиций должна начинаться с 1.

Товар должен быть только тот, который есть в прайс-листе. 

Цена и количество товара не должны быть отрицательными значениями. 

Ставка налога может быть либо 18%, либо 10%.

В правилах заполнения документов может быть оговорено, какую часть документа должен заполнять пользователь, а какие величины должна рассчитывать программа. Например, вспомним, расчёт суммы в строке с товаром или алгоритм расчёта стоимости услуг. 

Вот ещё правила. В счёте должна быть хотя бы одна позиция с товаром. Услуги можно выставлять только вместе с товарами.

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

Перечисленные требования можно разделить на три группы. 

Первая группа. Требования, которые предписывают обязательно заполнять поля. Если пользователь оставил поле незаполненным, то при сохранении данных в таблице ему будет автоматически присвоено значение NULL.

Вторая группа. Предписывают проверять вводимые значения. 

В третью группу войдут правила, требующие дополнительных расчётов. В простейшем случае это вычисление арифметических выражений. В более сложных ситуациях это проверки и вычисление по какому-либо алгоритму.

Для реализации правил первой и второй группы в ORACLE предусмотрен механизм декларативной целостности данных.

Для правил третьей группы придётся писать программный код и размещать его либо в программе-клиенте, либо на сервере базы данных. 

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

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

Проверка обязательности заполнения колонки.

Проверка значения на принадлежность к заданному диапазону.

Проверка значения на вхождение в список.

Комбинация из простых правил.

Более сложные.

Проверка на уникальность значений. 

Проверка на присутствие значения в другой таблице.

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

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

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

Рисунок 1. Таблица EXDOC с дубликатами.

Представим, как будет записана команда UPDATE на изменение только одной записи. Например, требуется изменить только вторую запись. Как бы мы ни пытались, но написать "простой" запрос, используя знания предыдущих уроков, вряд ли удастся. Команда будет изменять всё время обе записи. "Простым SQL’ем" мы её не достанем. 

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

Надо заметить, что есть ряд случаев, когда допускается дублирование записей. Это делается специально.

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

Рисунок 2. Пример заполнения таблицы EXDOC.

Мы договорились об обязательном заполнении колонки "Номер счёта". В повседневной жизни двух счетов с одинаковым номером в одной организации быть не может. Если такой случай произойдет, то это может привести к пересортице товара, сбою в отслеживании платежей и т.д. Скажем так: у каждого счёта должен быть свой номер, неповторимый, уникальный.

Это означает, что в колонке "Номер счёта" будут храниться уникальные значения. Одно значение дважды не встретиться. А коль это случится, то мы не будем иметь в таблице записи-дубликаты. Все записи будут уникальными, отличными друг от друга. 

Если существует правило уникальности, то про него говорят – первичный ключ. 

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

Например. Пусть в организации каждый год нумерация счётов начинается с 1. Следствием этого, будет тот факт, что счёт с номером 125 может быть выписан и в 2003 году, и в 2004 году. Это нарушит правило уникальности для колонки "Номер счёта" в таблице EXDOC. Хотя записи всё равно будут уникальны. Но уникальность будет достигаться комбинацией значений в колонках "Номер счёта" и "Дата счёта". 

Если рассматривать остальные колонки таблицы счетов, то про их уникальность можно говорить с большой натяжкой. 

Судите сами. На одного и того же покупателя может быть выписано несколько счетов. Это случай с постоянными покупателями. 

Адрес доставки может быть использован тоже в нескольких счетах. Поле не обязательно для заполнения и вследствие этого несколько записей будут иметь пустое значение. Пустое значение, NULL – это тоже значение! Запомните это свойство колонок, которые не обязательны для заполнения. Уникальности в них не ищите!

Совпадение по суммам носит вероятностный характер. Гарантий у нас нет. 

Что же дает правило уникальности? 

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

Если у таблицы правила уникальности нет, то следует настроиться на работу с группой записей. 

Естественно, что уникальность записей и дублирование записей это два несовместимых варианта. Всегда имеем один из них.

Обращаю внимание на то, что хранение записей в таблице с товарами и услугами имеет смысл, если для них есть соответствующая запись в таблице с шапкой счетов. Про такую зависимость между записями в двух таблицах, говорят: "Таблица EXPOS ссылается на EXDOC" или "Таблица EXDOC – главная, EXPOS - подчиненная”.

Очевидно следующее. 

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

Во-вторых. Перед добавлением записи в таблицу с позициями или услугами нужно проверять наличие записи в таблице EXDOC. 

В-третьих. Нельзя удалить запись из таблицы EXDOC, если у неё есть "ссылающиеся" записи в таблицах EXPOS и EXSVC.

Программист может организовать эти проверки с помощью правил ссылочной целостности. Это 5-ый тип правил декларативной целостности.

Как в базе данных реализовать уникальность записей?

С одной стороны просто, нужно выбрать комбинацию колонок для обеспечения уникальности. В случае с таблицами EXDOC и EXPOS это действительно просто. Рассмотрим более интересный вариант – таблицу с услугами EXSVC.

Рисунок 3. Таблица EXSVC с дополнительной колонкой.

Попробуем найти комбинацию, которая обеспечит уникальность. Это комбинация: номер счёта, дата счёта и название услуги. Уникальность достигается за счёт трех значений. 

Но! Ещё раз вспомним команды манипулирования данными. Для обращения к одной записи придется в условиях отбора записей перечислить комбинацию из трёх значений. Должны будем указать значения для колонки "Номер счёта", значение для колонки "Дата счёта" и строку для колонки "Название услуги". Это придётся делать каждый раз. В ряде случаев очень не удобно: долго набирать на клавиатуре; чревато ошибками; программный код становиться громоздким.

На практике делают так. Создают колонку специально для хранения уникального значения. Программисту очень удобно. Команды манипулирования данными содержат всего одно условие. 

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

Эта колонка "искусственно" добавлена, в реальности ей ничего нельзя сопоставить, это "суррогатное" значение. В отличие от комбинации значений номер счёта, дата счёта и название услуги, которая является "естественной", т.е. присутствует в реальности.

Теперь вам будут понятны термины "естественный первичный ключ" и "суррогатный ключ".

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

Ещё вариант заполнения таблицы услуг. Обратим внимание на записи для 152-го счёта. Видим, ситуацию дублирования записей. В действительности она не должна произойти. Если выписывается счёт, то в нем должна быть одна строка с услугами доставки. Использование суррогатного ключа позволяет получить такую ситуацию. По сути одна запись храниться дважды, хотя уникальность значений обеспечена за счёт колонки "Код записи". Для предотвращения этой ситуации необходимо наложить на таблицу два ограничения. Первое будет проверять значение в колонке "Код записи". Это первичный ключ. Второе проверяет уникальность в колонках "Номер счёта", "Дата счёта" и "Название услуги". Это правило - вторичный ключ. При отсутствии проверки естественной уникальности будем иметь дубликаты.