Констрейнты sql что это

Предложение CONSTRAINT

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

Предложение CONSTRAINT используется в инструкциях ALTER TABLE и CREATE TABLE для создания и удаления ограничений. Существует два типа предложений CONSTRAINT: для создания ограничения на одно поле и на несколько полей.

Примечание: Ядро СУБД Microsoft Access не поддерживает использование CONSTRAINT или любых других инструкций DDL с базами данных, которые не основаны на Microsoft Access. Вместо этого применяйте методы Create DAO.

Синтаксис

Ограничение на одно поле:

CONSTRAINT имя REFERENCES внешня я_таблица [( внешнее_поле1, внешнее_поле2)]
[ON UPDATE CASCADE | SET NULL]
[ON DELETE CASCADE | SET NULL]>

Ограничение на несколько полей:

Предложение CONSTRAINT включает в себя следующие элементы:

Имя создаваемого ограничения.

Имена полей, определяемых как первичный ключ.

Имена полей, определяемых как уникальный ключ.

Имена полей, в которых не может быть значений NULL.

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

Имя поля внешняя таблица которое содержит поле или поля, заданные полем внешнего поля.

Имена полей во внешней_таблице, заданных аргументами ссылка1, ссылка2. Если ссылка указывает на поле первичного ключа внешней_таблицы, данный аргумент можно опустить.

Замечания

Выражение для ограничения на одно поле указывается в предложении определения поля инструкции ALTER TABLE или CREATE TABLE непосредственно за спецификацией типа данных поля.

Выражение для ограничения на несколько полей указывается тогда, когда зарезервированное слово CONSTRAINT используется вне предложения определения поля в инструкции ALTER TABLE или CREATE TABLE.

В предложении CONSTRAINT можно задать для поля один из следующих типов ограничений:

Зарезервированное слово UNIQUE используется для назначения поля в качестве уникального ключа. Это значит, что две записи в таблице не могут иметь одно и то же значение в этом поле. Любое поле (или список полей) можно ограничить как уникальное. Если ограничение на несколько полей назначено уникальным ключом, объединенные значения всех полей в индексе должны быть уникальными, даже если несколько записей имеют одинаковое значение в одном из полей.

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

Примечание: Ограничение PRIMARY KEY не следует устанавливать в таблице, уже имеющей первичный ключ: это приведет к ошибке.

Зарезервированная фраза FOREIGN KEY используется для назначения поля в качестве внешнего ключа. Если первичный ключ внешней таблицы содержит больше одного поля, необходимо определить ограничение на несколько полей, указав все ссылающиеся поля, имя внешней таблицы и имена полей внешней таблицы, на которые указывают ссылки (в том же порядке, в котором перечислены ссылающиеся поля). Если поля, на которые указывают ссылки, являются первичным ключом внешней таблицы, указывать их не требуется. По умолчанию ядро СУБД считает, что ссылки указывают на первичный ключ внешней таблицы.

Ограничения внешнего ключа задают определенные действия, выполняемые при изменении значения соответствующего первичного ключа.

Можно задать действия, которые будут выполняться во внешней таблице, на основе действий, выполняемых в первичном ключе таблицы, для которой определено предложение CONSTRAINT. Пример определения для таблицы Customers:

CREATE TABLE Customers (CustId INTEGER PRIMARY KEY, CLstNm NCHAR VARYING (50))

Пример определения для таблицы Orders, в котором задается отношение внешнего ключа, ссылающееся на первичный ключ таблицы Customers:

CREATE TABLE Orders (OrderId INTEGER PRIMARY KEY, CustId INTEGER, OrderNotes NCHAR VARYING (255), CONSTRAINT FKOrdersCustId FOREIGN KEY (CustId) REFERENCES Customers ON UPDATE CASCADE ON DELETE CASCADE

Для внешнего ключа определены предложения ON UPDATE CASCADE и ON DELETE CASCADE. Предложение ON UPDATE CASCADE означает, что при обновлении в таблице Customers идентификатора клиента (CustId) будет выполнено каскадное обновление в таблице Orders. В каждый заказ, содержащий соответствующее значение идентификатора клиента, будет автоматически внесено новое значение. Предложение ON DELETE CASCADE означает, что при удалении клиента из таблицы Customers все строки таблицы Orders, содержащие его идентификатор, также будут удалены.

Другое определение для таблицы Orders, в котором вместо действия CASCADE используется SET NULL:

CREATE TABLE Orders (OrderId INTEGER PRIMARY KEY, CustId INTEGER, OrderNotes NCHAR VARYING (255), CONSTRAINT FKOrdersCustId FOREIGN KEY (CustId) REFERENCES Customers ON UPDATE SET NULL ON DELETE SET NULL

Предложение ON UPDATE SET NULL означает, что при обновлении в таблице Customers идентификатора клиента (CustId) соответствующие значения внешнего ключа в таблице Orders автоматически изменятся на NULL. Аналогично предложение ON DELETE SET NULL означает, что при удалении клиента из таблицы Customers все соответствующие внешние ключи в таблице Orders принимают значение NULL.

Чтобы предотвратить автоматическое создание индексов для внешних ключей, можно использовать модификатор NO INDEX. Определение внешнего ключа в такой форме должно применяться только в том случае, если получающиеся в результате значения индексов будут часто повторяться. Если значения в индексе внешнего ключа часто повторяются, использование индекса может быть менее эффективно, чем простое сканирование таблицы. Сохранение такого индекса при вставке и удалении строк таблицы снижает производительность и не приносит никакой пользы.

Источник

Ограничения (сonstraints) PostgreSQL: exclude, частичный unique, отложенные ограничения и др

Целостность данных легко нарушить. Бывает так, что в поле price попадает значение 0 из-за ошибки в коде приложения (периодически всплывают новости, как в том или ином инет-магазине продавали товары по 0 долларов). Или бывает, что удалили юзера из таблицы, но какие-то данные о нем остались в других таблицах, и эти данные вылезли в каком-то интерфейсе.

PostgreSQL, как и любая другая СУБД, умеет делать некоторые проверки при вставке/изменении данных, и этим обязательно нужно уметь пользоваться. Давайте посмотрим, что мы можем проверять:

1. Кастомный подтип через ключевое слово DOMAIN

В PostgreSQL вы можете создать свой тип, основанный на каком-нибудь int или text с дополнительной проверкой каких-то вещей:

Мы создаем тип us_postal_code, в котором регулярками проверяем различные варианты его написания. Теперь никто не сможет туда по ошибке написать “улица Бармалеева”, там будет только индекс:

Кроме того, это улучшает читабельность кода, так как тип сам поясняет, что в нем лежит, в отличие от безликих integer или text.

2. Check (особенно актуально для проверки jsonb и hstore)

Выше мы использовали us_postal_code использовали оператор CHECK. Точно такой же можно написать и в конструкции CREATE TABLE.

Или в таблице с товарами можно поставить check (price > 0), тогда вы не будете продавать ноуты по 0 рублей. Или можно написать хранимку и использовать check(superCheckFunction(price)), а в этой хранимке кучу логики проверять.

Кстати, тип varchar(100) — это тоже самое, что и тип text с дополнительным check по длине.
Надо понимать, что check происходит при каждом insert или update, поэтому, если в вашу таблицу идет 100500 записей в секунду, то check возможно делать не стоит.

Бывает важно обвешать чеками универсальные типы данных, такие как jsonb или hstore, потому что туда можно напихать что угодно. Можно проверять существование каких-то ключей в json или что их значение соответствует тому, что там должно быть.

3. Проверка на уникальность, как простая, так и частичная.

Простая проверка, что email у разных пользователей должен быть разный:

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

Вместо простого UNIQUE вы можете добавить такой уникальный индекс:

Тогда уникальность email будет проверяться только у неудаленных юзеров. В where можно вставлять любые условия.

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

4. EXCLUDE

С помощью оператора EXCLUDE можно сделать еще один вид уникальности. Дело в том, что в посгресе множество типов данных, как встроенных, так и добавляемых через расширения. Например, есть тип данных ip4r, с его помощью можно хранить диапазон ip-адресов в одном поле.

И, допустим, надо в таблице хранить непересекающиеся диапазоны. Вообще, проверить, пересекаются ли два диапазона можно с помощью оператора &&, например SELECT ‘127.0.0.0/24’ && ‘127.0.0.1/32’ вернет true.

В итоге делаем просто:

И тогда при вставке/апдейте postgres будет смотреть каждую строку, не пересекается ли она со вставляемой (т.е. не возвращает ли использование оператора && истину). Благодаря gist индексу эта проверка очень быстрая.

5. NOT NULL

Тут всё понятно, колонка не может принимать значение NULL. Зачастую (но необязательно) идет в связке с DEFAULT.

При добавлении новой колонки с not null в существующую таблицу надо быть осторожным. Дело в том, что обычную колонку, где допустимо null, PostgreSQL добавляет мгновенно, даже если таблица очень большая, к примеру, десятки миллионов строк. Потому что ему не надо физически менять данные, лежащие на диске, null в postgres не занимают места. Однако если вы добавите колонку name text not null default ‘Вася’, то посгрес по факту полезет делать update каждой строки, и это может занять много времени, что может быть недопустимо в некоторых ситуациях.

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

6. Primary key, т.е. первичный ключ

Раз это первичный ключ, то оно должен быть уникальным и не может быть пустым. В общем, в PostgreSQL PRIMARY KEY работает как комбинация UNIQUE и NOT NULL.

В других базах данных PRIMARY KEY делает еще и другие вещи, к примеру, если не ошибаюсь, в MySQL (Innodb), данные еще и автоматически кластеризуются вокруг PK для ускорения доступа по этому полю. (В посгресе, кстати, тоже так можно сделать, но вручную, командой CLUSTER. Но обычно в этом нет необходимости)

7. FOREIGN KEY

Например, у вас есть таблица

и таблица со статусами

Вы можете указать базе, что колонка status_id соответствует Id из таблице status_dictionary. Например, так:

Теперь вы сможете в status_id записать только null или Id из таблицы status_dictionaries, и больше ничего.

Также можно это делать по двум полям:

При вставке опять же есть некоторый оверхед, потому что при каждой вставке СУБД вынуждена лочить довольно много вещей. Поэтому при (очень) интенсивной вставке возможно не стоит злоупотреблять использовнием Foreign key

8. DEFERRABLE

Если для производительности надо отложить проверку констрейнтов, констрейнты можно пометить ключевым словом DEFERRABLE.

Они бывают разных видов, например если вы сделаете UNIQUE(email) DEFERRABLE INITIALLY DEFERRED, то внутри транзакции можно написать

И тогда все проверки будут Отложены и по факту произойдут только перед словом commit
Это сработает для UNIQUE, PRIMARY KEY и REFERENCES, но не сработает для NOT NULL и CHECK.

Источник

CHECK CONSTRAINT в MS SQL — Грабли по которым мы прошлись

Данная статья будет про то, как одна дружная команда веб разработчиков, не имея в своём составе опытного SQL разработчика, добавила Check Constraint в таблицу и прошлась по нескольким простым, но не сразу очевидным граблям. Будут разобраны особенности синтаксиса T-SQL, а также нюансы работы ограничений (СONSTRAINT’ов), не зная которые, можно потратить не мало времени на попытки понять, почему что-то работает не так. Так же будет затронута особенность работы SSDT, а именно как генерируется миграционный скрипт, при необходимости добавить или изменить ограничения (CONSTRAINT’ы).

Дабы читатель поскорей понял, стоит читать статью или нет, я сначала рассмотрю абстрактную задачу, по ходу решения которой будут заданы вопросы «А почему так?». Если вы сразу будете знать ответ, то смело бросайте чтение и переходите к следующей статье.

Разработаем гарем?

В последний момент, приходит озарение, что на уровне схемы базы мы не гарантируем существование только одного мужчины в гареме. Решаем это исправить путём добавления проверочного ограничения (check constraint):

основанного на скалярной пользовательской функции (scalar-valued Function):

«А почему так?» №1.

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

«А почему так?» №2.

После того как побороли проблему вставки данных, решаем задеплоиться в QA окружение. Создаём миграционный скрипт при помощи SSDT, быстро просматриваем его. Находим какую-то не очень понятную строку (выделена красной рамкой).

Из комментария в инструкции PRINT кажется, что это запуск проверки ограничения на уже существующих строках. Но при создании ограничения мы же указали, что существующие строки проверять не нужно («Check Existing Data On Creation Or Re-Enabling» был установлен в “No”). Поэтому начинаем гуглить и находим «полезный» пост. Прочитав ответ и все комментарии к нему, обретаем глубокую уверенность, что эта инструкция включает проверку при вставке новых строк, а не валидирует существующие, т. е. нам обязательно нужно оставить эту строку, иначе ограничение вообще никогда не будет проверяться.
С гордостью за проделанную работу, отправляем скрипт, ждёмс… Спустя Х часов приходит отчёт, что наш миграционный скрипт успешно провалился. Смотрим отчёт.

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

«А почему так?» №1 – Объяснение.

Тут всё предельно просто. Мы забыли, что проверка условий CHECK CONSTRAINT’а происходит уже после вставки строки в таблицу и в момент вставки первого мужчины в гарем, правильным условием будет равенство единице, а не нулю. В итоге функция была переписана на много проще.

«А почему так?» №2 – Объяснение.

Тут всё оказалось не столь прозрачно. Сначала пришлось всё-таки разобраться в истинном назначении упавшей инструкции. И, к превеликому нашему удивлению, мы поняли, что она делает именно то, что сказано в комментарии, а не то, что описано в найденном «полезном» посте (разбор синтаксиса будет ниже).
Узнав это, было логично предположить, что при создании миграционного скрипта была выбрана база, в которой на CK_Persons значение «Check Existing Data On Creation Or Re-Enabling» было “Yes”, а не “No”. Но эта теория провалилась. Меняя это значение и генерируя новый скрипт, стало понятно, что SSDT, вообще игнорируют это значение. Начали грешить на наличие бага в SSDT.
Очередной этап поисков навёл нас на следующий пост, из которого мы уже поняли, что это «фича, а не баг».
Согласно дизайна SSDT, при создании скрипта всегда создаётся ограничение, которое включено, т.е. проверяется для всех будущих INSERT/UPDATE. За это отвечает первая инструкция ALTER в нашем миграционном скрипте.
Вторая же инструкция ALTER (выделена красной рамкой) отвечает за валидацию существующих данных и является опциональной. За то будет ли эта инструкция добавлена в миграционный скрип, отвечает специальная опция генерации скрипта:

Включив её, мы для каждого нового миграционного скрипта активируем валидацию существующих данных, т.е. в него будет вставлена опциональная инструкция (второй ALTER). Иначе, инструкция попросту отсутствует и на существующих данных проверка не выполняется. Как это не прискорбно получается, но SSDT генерирует миграционный скрипт по принципу всё или ничего. Можно либо для всех вновь добавляемых ограничений включить проверку на существующих данных, либо для всех её пропустить. Для более тонкой настройки поведения придётся править скрипт вручную.

Ограничения (Constraints) в MS SQL

Примеры использования sys.check_constraints

Можно получить ответ в более привычном формате, воспользовавшись оператором UNPIVOT:

Особенности работы CHECK CONSTRAINT:

Синтаксис CHECK CONSTRAINT

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

Добавление нового CHECK CONSTRAINT

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

Примеры команд будут приведены для простейшей таблицы Employees, которая выглядит следующим образом:

Изменение существующего CHECK CONSTRAINT

Недокументированное поведение

Есть ряд случаев, когда выполнение команд приводит к неожиданным результатам. Причём я не смог найти объяснение на сайте msdn.
Что бы это увидеть, необходимо рассмотреть все возможные комбинации состояний в сочетании со всеми возможными вариантами команд. Тогда будет видно, что в 5-ти случаях получаемое значение свойства «Check Existing Data» не соответствует ожиданиям.

(*) Значение свойства «Check Existing Data» может быть переведено из значения «Yes» в значение «No», только если текущее значение свойства «Enforce For INSERTs And UPDATEs» отличается от заданного в команде.

(**) «Check Existing Data» может быть «Yes», только если ограничение включено (Enforce For INSERTs And UPDATEs = “Yes”). Т. е. в команде WITH CHECK NOCHECK часть WITH CHECK будет проигнорирована и «Check Existing Data» не будет установлено в «Yes». Это так же объясняет почему в качестве начальных состояний есть только 3 варианта для каждой команды (а не 4).

Удаление существующего CHECK CONSTRAINT

Команда очень проста и не требует дополнительных объяснений. Ещё шаблон:

Источник

SQL Ключевое слово CONSTRAINT

SQL ADD CONSTRAINT

Команда ADD CONSTRAINT используется для создания ограничения после того, как таблица уже создана.

Следующий SQL добавляет ограничение с именем «PK_Person», которое является ограничением первичного ключа для нескольких столбцов (ID и фамилия):

Пример

DROP CONSTRAINT

Команда DROP CONSTRAINT используется для удаления уникального, первичного ключа, внешнего ключа или ограничения проверки.

Отбросьте уникальное ограничение

Чтобы удалить уникальное ограничение, используйте следующий SQL:

SQL Server / Oracle / MS Access:

Отбросьте ограничение первичного ключа

Чтобы удалить ограничение первичного ключа, используйте следующий SQL:

SQL Server / Oracle / MS Access:

Удаления ограничения внешнего ключа

Чтобы удалить ограничение внешнего ключа, используйте следующий SQL:

SQL Server / Oracle / MS Access:

Отбросьте ограничение проверки

Чтобы удалить ограничение проверки, используйте следующий SQL:

SQL Server / Oracle / MS Access:

Упражнения

Тесты

КАК СДЕЛАТЬ

ПОДЕЛИТЬСЯ

СЕРТИФИКАТЫ

Сообщить об ошибке

Если вы хотите сообщить об ошибке или сделать предложение, не стесняйтесь, присылайте нам электронное письмо:

Ваше предложение:

Спасибо, за вашу помощь!

Ваше сообщение было отправлено в SchoolsW3.

Топ Учебники

Топ Справочники

Топ Примеры

Веб Сертификаты

Источник

Сложное ограничение целостности (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 года.

Источник