Сложное ограничение целостности (constraint)
Однажды возникла необходимость организовать сложный контроль вводимой одновременно в несколько таблиц логически связанной информации в БД ORACLE. Преобразование начального логически корректного набора данных в таблицах в конечный логически корректный набор производится последовательностью DML операторов. При этом проводить модификацию может произвольный клиент поведение которого неконтролируемо а структура данных такова, что в процессе выполнения пошаговой модификации на некоторых шагах набор данных может становится логически ошибочным.
Простейший пример это таблица истории значений из трех полей: значение, начальная дата действия значения, конечная дата действия значения. Логически корректная история не может иметь записей у которых перекрываются периоды действия значений. Для изменения границы действия двух соседних значений необходимо изменить две даты — дату конца действия предыдущего значения в предыдущей записи и дату начала действия последующего в следующей записи. Если границу смены значений двигать по времени вперед и на первом шаге переставить вперед дату окончания действия значения первой записи получим логически ошибочный набор данных. Именно поэтому решить задачу табличными триггерами невозможно — они срабатывают по каждому оператору модификации данных.
Реальная задача слегка отличается от простейшего примера. Набор данных разложен в десятке таблиц, алгоритм бизнес правил контроля вылился в процедуру на 400 строк с обращением через линки к API на других серверах.
Для реализации такого контроля нужен был триггер, который срабатывает только один раз в транзакции по событию COMMIT, с возможностью отката транзакции по результату отработки процедуры контроля бизнес логики. Такой триггер нашелся.
Рассмотрим на примере подробности реализации. Набор данных.
Данные примера является набором сущностей типа — «Сотрудник» с информацией о подразделении и месте расположения подразделения. Попробуем реализовать для этих данных бизнес правило ограничивающее количество сотрудников с должностью ‘CLERK’ в одном городе не более 2х.
В общем случае правил бизнес контроля может быть несколько а в одной транзакции проходить модификация информации нескольких сотрудников. Соответственно на момент commit-а нам надо иметь два набора информации:
— набор полей которые были модифицированы определит список бизнес правил которые должны быть проконтролированы;
— набор идентификаторов сотрудников подлежащих контролю.
Практический список правил бизнес контроля и их сложность позволяют без критической нагрузки на сервер осуществлять проверку каждого модифицированного сотрудника по всем реализованным правилам. Это допущение позволит в нашем случае упростить реализацию ограничения целостности.
Создаем таблицу которая будет содержать набор идентификаторов сотрудников модифицированных текущей транзакцией.
На все таблицы содержащие информацию для правила контроля вешаем триггера которыми будем вставлять в emp_chk идентификаторы модифицированных сотрудников. Некоторые комментарии к триггерам. Заказчик боевого применения функционала контроля потребовал совместимость с ORACLE-9, поэтому триггера не составные (compound).
Возможность отключения констрэйнта реализована пакетной функцией var_chk.chk_on. Использование для этой цели функции дает возможность управлять контролем не только статически (через конфигурационную таблицу) но и динамически (например для разных сеансов БД). Полный текст пакета будет приведен позже.
Использование MERGE вызвано желанием проводить модификацию одним оператором. Поле emp_chk.i является платой за использование MERGE т.к. написать MERGE без фразы WHEN MATCHED не удалось.
Очистку таблицы emp_chk в начале новой транзакции осуществляют следующие триггера. Управляет очисткой пакетная переменная var_chk.first_dml_in_commit:
Создаем materialized view.
Триггер инициализирующий var_chk.first_dml_in_commit обеспечивает очистку EMP_CHK в начале транзакции.
Собственно триггер запускающий бизнес контроль.
Некоторые комментарии к тексту CHK_EMP_MV_AS. Отладка и контроль функционирования констрэйнта можно облегчить логированием. Учтем, что в случае возникновения ошибки набор данных предъявленный для commit-а обычно откатывается и теряется. В данной реализации в лог пишется не только итоговый статус обработки но и весь набор данных сотрудника подвергшегося модификации предъявленный для commit-a независимо от результата обработки. Снимки наборов данных помещаются в поле emp_chk_log.XML. Лог пишется пакетной функцией var_chk.write_log в таблицу:
Все бизнес правила реализованы в отдельной функции emp_logic. Функция не является членом пакета. Это позволяет разделить в разработке и сопровождении бизнес правила констрйнта и слой системных механизмов его функционирования. Ниже текст пакета var_chk.
Функция контроля бизнес правил.
Проверим велосипед на ходу.
Реальная реализация этого решения работает на трех десятках серверов центрального офиса и филиалов с весны 2015 года.
Ограничения уникальности и проверочные ограничения
В этом разделе содержатся следующие подразделы.
Ограничения UNIQUE
Ограничения представляют собой правила, которые принудительно применяются в Компонент SQL Server Database Engine от имени пользователя. Например, ограничения UNIQUE можно использовать для обеспечения того, чтобы в указанные столбцы, не входящие в состав первичного ключа, не вводились повторяющиеся значения. Хотя уникальность значений ограничения UNIQUE и PRIMARY KEY гарантируют в равной степени, в случае, когда необходимо обеспечить уникальность в столбце или комбинации столбцов, которые не являются первичными ключевыми, вместо ограничения PRIMARY KEY следует использовать ограничение UNIQUE.
В отличие от PRIMARY KEY, ограничения UNIQUE допускают значение NULL. Однако, как и всякое другое значение столбца с ограничением UNIQUE, NULL может встречаться только один раз. На ограничение UNIQUE могут ссылаться ограничения FOREIGN KEY.
При добавлении ограничения UNIQUE на уже существующий столбец или группу столбцов в таблице, компонент Компонент Database Engine по умолчанию проверяет уникальность всех существующих значений в указанных столбцах. При попытке добавить ограничение UNIQUE к столбцу, содержащему повторяющиеся значения, компонент Компонент Database Engine возвращает ошибку, а ограничение не добавляется.
Компонент Компонент Database Engine автоматически создает индекс UNIQUE, что обеспечивает выполнение требований уникальности значений для ограничений UNIQUE. Поэтому, при попытке вставки в таблицу строки с повторяющимися данными, компонент Компонент Database Engine выдает сообщение об ошибке, в котором сообщается о нарушении ограничения UNIQUE, а строка в таблицу не вставляется. Для обеспечения выполнения ограничения UNIQUE по умолчанию создается уникальный некластеризованный индекс, если явно не указано создание кластеризованного индекса.
Ограничения CHECK
Проверочные ограничения подобны ограничениям внешнего ключа, так как они управляют значениями, которые присваиваются столбцу. Однако они по-разному определяют допустимые значения: ограничения внешнего ключа получают список допустимых значений из другой таблицы, а проверочные ограничения определяют допустимые значения по логическому выражению.
Ограничения, которые включают явное или неявное преобразование данных, могут вызывать ошибки в операциях такого рода. Например, ограничения, заданные для таблиц, которые являются исходными при переключении секций, могут приводить к ошибкам при использовании оператора ALTER TABLE. SWITCH. Следует избегать преобразования типов данных в определениях ограничений.
Ограничения проверочных ограничений
Проверочные ограничения отклоняют значения, вычисляемые в FALSE. Поскольку значения NULL вычисляются как UNKNOWN, то их наличие в выражениях может переопределить ограничение. Например, предположим, что на столбец MyColumn типа int установлено следующее ограничение: MyColumn может содержать только значение 10 (MyColumn=10). При вставке значения NULL в столбец MyColumn компонент Компонент Database Engine вставит значение NULL и не возвратит ошибку.
Проверочное ограничение возвращает TRUE, если для проверяемого условия в любой строке таблицы отсутствует значение FALSE. Проверочное ограничение работает на уровне строки. Если в только что созданной таблице отсутствуют строки, то любое проверочное ограничение на эту таблицу считается допустимым. В результате могут возвращаться неожиданные результаты, как в следующем примере.
Ограничение CHECK показывает, что в таблице CheckTbl должна быть хотя бы одна строка. Однако поскольку в таблице нет ни одной строки, над которой можно было бы произвести проверку ограничения, инструкция ALTER TABLE завершается успешно.
Инструкция DELETE выполняется успешно, даже если ограничение CHECK определяет, что в таблице CheckTbl должна быть хотя бы 1 строка.
Связанные задачи
Если таблица опубликована для репликации, то изменения схемы следует проводить при помощи инструкции языка Transact-SQL ALTER TABLE или объектов SMO. При изменении схемы с помощью конструктора таблиц или конструктора диаграмм баз данных конструктор пытается удалить и затем вновь создать таблицу. Но поскольку удалять опубликованные объекты нельзя, изменения схемы не будут применены.
SQL — Ограничения
Дата публикации: 2017-12-01
От автора: ограничения SQL — это правила, применяемые к столбцам данных таблицы. Они используются, чтобы ограничить типы данных, которые могут храниться в таблице. Это обеспечивает точность и надежность данных в базе данных.
Ограничения могут применяться либо на уровне столбцов, либо на уровне таблицы. Ограничения на уровне столбца применяются только к одному столбцу, тогда как ограничения уровне таблицы применяются ко всей таблице.
NOT NULL Constraint — столбец не может иметь значение NULL.
DEFAULT Constraint — задает значение по умолчанию для столбца, если оно не указано.
Бесплатный курс по PHP программированию
Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC
В курсе 39 уроков | 15 часов видео | исходники для каждого урока
UNIQUE Constraint — все значения в столбце должны быть разными.
PRIMARY Key — уникальная идентификация каждой строки/записи в таблице базы данных.
FOREIGN Key — уникально идентифицирует строку/запись в любой другой таблице базы данных.
CHECK Constraint — ограничение CHECK обеспечивает, чтобы все значения в столбце удовлетворяли определенным условиям.
INDEX — используется для быстрого создания данных базы данных.
Ограничения могут указываться при создании таблицы с помощью оператора CREATE TABLE или вы можете использовать оператор ALTER TABLE для создания ограничений уже после создания таблицы.
Удаление ограничений
Любое ограничение, которое вы определили, можно удалить с помощью команды ALTER TABLE с параметром DROP CONSTRAINT.
Например, чтобы удалить ограничение первичного ключа в таблице EMPLOYEES, вы можете использовать следующую команду.
Ограничения целостности. Представления данных
Добавление ограничения с отказом от проверки соответствия имеющимся данным
То есть ограничение на изменение данных действует, но в таблице встречаются его нарушения (возникшие ранее).
Такая возможность добавить ограничение без проверки соответствия ему имеющихся данных позволяет:
Приостановка проверки заявляемых ограничений в пределах транзакции
В этом случае станет возможным отложить автоматическую проверку ограничения на период до завершения текущей транзакции (подобная степень долговечности отмены проверки оправдывает употребление термина «приостановка»). С этой целью в сеансе связи с СУБД следует выдать:
Теперь можно внести изменение, нарушающее ограничение:
Если дубликат не будет устранен, то сообщение об ошибке появится только при попытке выполнить COMMIT или же явочным путем возобновить конкретную проверку:
Вместо приостановки/возобновления проверки нескольких ограничений по отдельности можно выполнить эти действия для всех ограничений зараз, например:
Ограничение для столбца A нормально не проверяется (умолчательно приостановлено) и временно допускает вступление в силу. Ограничение для столбца B нормально проверяется и временно допускает приостановку действия. Само указание слов INITIALLY IMMEDIATE является умолчательным.
Упражнение. Проверьте срабатывание указанных для столбцов таблицы X условий на заносимые значения.
Приостановка проверки ограничений может использоваться:
Не все специалисты в реляционном подходе к проектированию БД разделяют мнение о целесообразности приостановки проверки ограничений, принятой в SQL, указывая на другие способы решения возникающих проблем. Нельзя не заметить, что во время приостановки действия ограничений БД может давать на запросы ответы, неправильные с точки зрения заложенной в нее модели предметной области. В любом случае подобная приостановка должна использоваться программистом исключительно как временная техническая мера.
«Долговременное» отключение ограничений целостности
Действующее в схеме объявляемое ограничение целостности можно отключить также на период, более долгий, чем транзакция, вообще безотносительно к последующим транзакциям:
Мотивами для «долговременного» отключения проверки ограничений могут, например, стать:
Допускается задавать отключенность ограничения изначально при его создании.
Технология включения и выключения заявляемых ограничений целостности
Если нормальный перевод ограничения в состояние ENABLED невозможен из-за накопленных противоречащих ограничению данных, Oracle предлагает два практических выхода из подобной ситуации:
Оба решения представляют первоочередную ценность для больших таблиц.
Включение ограничения без выполнения проверки имеющихся данных
В некоторых случаях можно включить ограничение, отказавшись от проверки накопленных данных, и тогда оно будет проверяться только при внесении новых изменений в таблицу. При этом в таблице, возможно, сохранятся от прежних времен записи, нарушающие ограничение:
Выявление записей, нарушающих ограничение
Другая возможность состоит в том, чтобы при наличии строк, нарушающих ограничение, получить в распоряжение список физических адресов таких строк. Для этого:
Если нарушения ограничения CTEST данными обнаружится, в программу вернется ошибка, но при этом в указанную в команде таблицу EXCEPTIONS СУБД занесет список физических адресов строк, нарушающих ограничение. После корректировки данных в таблице T соответствующие ей строки в EXCEPTIONS нужно не забыть удалить самостоятельно, так как Oracle, разумеется, этого не сделает.
Ограничения целостности. Представления данных
Добавление ограничения с отказом от проверки соответствия имеющимся данным
То есть ограничение на изменение данных действует, но в таблице встречаются его нарушения (возникшие ранее).
Такая возможность добавить ограничение без проверки соответствия ему имеющихся данных позволяет:
Приостановка проверки заявляемых ограничений в пределах транзакции
В этом случае станет возможным отложить автоматическую проверку ограничения на период до завершения текущей транзакции (подобная степень долговечности отмены проверки оправдывает употребление термина «приостановка»). С этой целью в сеансе связи с СУБД следует выдать:
Теперь можно внести изменение, нарушающее ограничение:
Если дубликат не будет устранен, то сообщение об ошибке появится только при попытке выполнить COMMIT или же явочным путем возобновить конкретную проверку:
Вместо приостановки/возобновления проверки нескольких ограничений по отдельности можно выполнить эти действия для всех ограничений зараз, например:
Ограничение для столбца A нормально не проверяется (умолчательно приостановлено) и временно допускает вступление в силу. Ограничение для столбца B нормально проверяется и временно допускает приостановку действия. Само указание слов INITIALLY IMMEDIATE является умолчательным.
Упражнение. Проверьте срабатывание указанных для столбцов таблицы X условий на заносимые значения.
Приостановка проверки ограничений может использоваться:
Не все специалисты в реляционном подходе к проектированию БД разделяют мнение о целесообразности приостановки проверки ограничений, принятой в SQL, указывая на другие способы решения возникающих проблем. Нельзя не заметить, что во время приостановки действия ограничений БД может давать на запросы ответы, неправильные с точки зрения заложенной в нее модели предметной области. В любом случае подобная приостановка должна использоваться программистом исключительно как временная техническая мера.
«Долговременное» отключение ограничений целостности
Действующее в схеме объявляемое ограничение целостности можно отключить также на период, более долгий, чем транзакция, вообще безотносительно к последующим транзакциям:
Мотивами для «долговременного» отключения проверки ограничений могут, например, стать:
Допускается задавать отключенность ограничения изначально при его создании.
Технология включения и выключения заявляемых ограничений целостности
Если нормальный перевод ограничения в состояние ENABLED невозможен из-за накопленных противоречащих ограничению данных, Oracle предлагает два практических выхода из подобной ситуации:
Оба решения представляют первоочередную ценность для больших таблиц.
Включение ограничения без выполнения проверки имеющихся данных
В некоторых случаях можно включить ограничение, отказавшись от проверки накопленных данных, и тогда оно будет проверяться только при внесении новых изменений в таблицу. При этом в таблице, возможно, сохранятся от прежних времен записи, нарушающие ограничение:
Выявление записей, нарушающих ограничение
Другая возможность состоит в том, чтобы при наличии строк, нарушающих ограничение, получить в распоряжение список физических адресов таких строк. Для этого:
Если нарушения ограничения CTEST данными обнаружится, в программу вернется ошибка, но при этом в указанную в команде таблицу EXCEPTIONS СУБД занесет список физических адресов строк, нарушающих ограничение. После корректировки данных в таблице T соответствующие ей строки в EXCEPTIONS нужно не забыть удалить самостоятельно, так как Oracle, разумеется, этого не сделает.
