что такое constraint sql

Ограничения (с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.

Источник

SQL сдерживает

ТРУДНОСТИ

SQL CONSTRAINTS используются для реализации правил таблицы. Если какое-либо нарушение ограничений вызвало какое-либо действие, не выполняющееся над таблицей, действие отменяется ограничением.

Некоторые CONSTRAINTS могут использоваться вместе с оператором SQL CREATE TABLE.

Общая структура SQL CONSTRAINT определяется как:

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

Типы SQL-ограничений

SQL предоставляет следующие типы ограничений:

Синтаксис:

Параметры:

Содержание:

SQL CREATE TABLE с ограничением для исключения значения NULL

В следующем разделе будет описано, как NOT NULL CONSTRAINT подтверждает, что столбец не может иметь значение NULL в инструкции CREATE TABLE.

Пример:

В следующем примере создается таблица. Вот имя поля и типы данных:

можно использовать следующий оператор SQL:

Код SQL:

Чтобы увидеть структуру созданной таблицы:

Код SQL:

SQL CREATE TABLE для проверки уникального значения

В следующем разделе мы обсудим, как SQL UNIQUE CONSTRAINT гарантирует, что каждая строка для столбца имеет разные значения в операторе CREATE TABLE.

Пример:

В следующем примере создается таблица. Вот имя поля и типы данных:

можно использовать следующий оператор SQL:

Код SQL:

Чтобы увидеть структуру созданной таблицы:

Код SQL:

SQL CREATE TABLE для проверки уникального значения на нескольких столбцах

В следующем примере создается таблица. Вот имя поля и типы данных:

можно использовать следующий оператор SQL:

Код SQL:

Чтобы увидеть структуру созданной таблицы:

Код SQL:

SQL CREATE TABLE с уникальным ограничением

Ограничение SQL UNIQUE используется для обеспечения того, чтобы каждая строка столбца имела различное значение. На этой странице мы собираемся обсудить, как работает SQL UNIQUE CONSTRAINT, если он используется в конце инструкции CREATE TABLE вместо использования UNIQUE CONSTRAINT в определенных столбцах.

Пример :

В следующем примере создается таблица. Вот имя поля и типы данных:

можно использовать следующий оператор SQL :

Код SQL:

Чтобы увидеть структуру созданной таблицы:

Код SQL:

SQL CREATE TABLE с ограничением CHECK

SQL CHECK CONSTRAINT гарантирует, что значение для определенного столбца или столбцов удовлетворяет заданному условию.

Задача ограничения CHECK заключается в ограничении значений для столбца таблицы.

Замечания :

В следующем примере создается таблица. Таблица содержит CHECK CONSTRAINT для столбца комиссии. Ограничение гарантирует, что «комиссия» должна быть меньше 1. Вот имя поля и типы данных:

можно использовать следующий оператор SQL:

Код SQL:

Чтобы увидеть структуру созданной таблицы:

Код SQL:

Выход :

SQL CREATE TABLE с использованием ограничения по умолчанию

SQL DEFAULT CONSTRAINT предоставляет значение по умолчанию, если для столбца не задано значение none.

Пример:

1. «Working_area» должно быть «Mumbai», если для этого столбца не указано ни одного,

можно использовать следующий оператор SQL:

Код SQL:

Чтобы увидеть структуру созданной таблицы:

Код SQL:

Выход :

SQL CREATE TABLE с использованием значений по умолчанию и CHECK CONSTRAINT

В следующем разделе мы обсудили использование SQL DEFAULT CONSTRAINT и использование SQL CHECK CONSTRAINT при создании таблицы.

Пример:

2. «Working_area» должно быть «Mumbai», если для этого столбца не указано ни одного,

можно использовать следующий оператор SQL:

Код SQL:

Чтобы увидеть структуру созданной таблицы:

Код SQL:

Выход :

SQL CREATE TABLE с оператором CHECK CONSTRAINT и IN

В следующем разделе мы обсудили, как использовать оператор SQL IN вместе с SQL CHECK CONSTRAINT.

Условие CHECK CONSTRAINT может быть определено с использованием любого из базовых операторов сравнения, таких как ( >, =, ), а также операторов BETWEEN, IN, LIKE и NULL.

Пример:

1. Первый столбец находится в столбце «working_area», который гарантирует, что works_area должен быть либо «London», либо «Brisban», либо «Chennai», либо «Mumbai»,

В следующей таблице имена полей и типы данных:

можно использовать следующий оператор SQL:

Код SQL:

Чтобы увидеть структуру созданной таблицы:

Код SQL:

SQL CREATE TABLE с оператором CHECK CONSTRAINT и LIKE

В следующем разделе мы обсудим, как можно использовать оператор SQL LIKE с CHECK CONSTRAINT.

Пример:

можно использовать следующий оператор SQL:

Код SQL:

Чтобы увидеть структуру созданной таблицы:

Код SQL:

Выход :

SQL CREATE TABLE с оператором CHECK CONSTRAINT и OR

В следующей теме мы обсуждаем использование оператора OR вместе с CHECK CONSTRAINT.

Пример:

можно использовать следующий оператор SQL:

Код SQL:

Чтобы увидеть структуру созданной таблицы:

Код SQL:

Выход :

SQL CREATE TABLE с использованием оператора CHECK CONSTRAINT и оператора AND, OR

В следующей теме мы обсудим использование операторов OR и AND вместе с CHECK CONSTRAINT. Условие начнет работать во время вставки записей в таблицу.

Пример:

во время создания таблицы, имена полей и типы данных которых

можно использовать следующий оператор SQL:

Код SQL:

Чтобы увидеть структуру созданной таблицы:

Код SQL:

Выход :

SQL CREATE TABLE с использованием CASCADE

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

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

Пример:

Таблица содержит PRIMARY KEY для «itemcode» и FOREIGN KEY для столбца «company_id», который ссылается на столбец «company_id» таблицы «company».

можно использовать следующий оператор SQL:

Код SQL:

Чтобы увидеть структуру созданной таблицы:

Код SQL:

Выводы указанного оператора SQL, показанного здесь, взяты с использованием Oracle Database 10g Express Edition.

Упражнения по SQL

Хотите улучшить вышеуказанную статью? Вносите свои заметки / комментарии / примеры через Disqus.

Предыдущая: Внешний ключ
Далее: Изменить таблицу

Источник

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 (Microsoft Access SQL)

Область применения: Access 2013, Office 2013

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

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

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

Синтаксис

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

CONSTRAINT name foreigntable [(foreignfield1, foreignfield2)] [ON UPDATE CASCADE | SET NULL] [ON DELETE CASCADE | SET NULL]>

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

CONSTRAINT name primary1[, primary2 [, …]]) | UNIQUE (unique1[, unique2 [, …]]) | NOT NULL (notnull1[, notnull2 [, …]]) | FOREIGN KEY [NO INDEX] (ref1[, ref2 [, …]]) REFERENCES foreigntable [(foreignfield1 [, foreignfield2 [, …]])] [ON UPDATE CASCADE | SET NULL] [ON DELETE CASCADE | SET NULL]>

Предложение CONSTRAINT состоит из следующих частей:

Имя ограничения, которое необходимо создать.

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

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

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

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

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

Имя поля или полей в foreigntable, определяемых ref1, ref2. Это предложение можно опустить, если поле, на которое ссылаются, представляет собой первичный ключ foreigntable.

Комментарии

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

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

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

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

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

Не используйте ограничение PRIMARY KEY в таблице, в которой уже есть первичный ключ; если вы сделаете это, возникнет ошибка.

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

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

Рассмотрим следующее определение таблицы «Заказы», которое определяет отношение внешнего ключа, ссылающегося на первичный ключ таблицы «Клиенты»:

Предложения ON UPDATE CASCADE и ON DELETE CASCADE определяются для внешнего ключа. Предложение ON UPDATE CASCADE означает, что если в таблице «Клиенты» обновляется идентификатор клиента (CustId), будет выполняться каскадное обновление таблицы «Заказы». Каждый заказ, содержащий соответствующее значение идентификатора клиента, будет автоматически обновляться новым значением. Предложение ON DELETE CASCADE означает, что если клиент удаляется из таблицы «Клиенты», все строки в таблице «Заказы», содержащие то же значение идентификатора клиента, также будут удалены. Рассмотрим другое определение таблицы «Заказы», в котором вместо действия CASCADE используется SET NULL:

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

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

Пример

В этом примере создается новая таблица с именем ThisTable и двумя текстовыми полями.

В этом примере создается новая таблица с именем MyTable с двумя текстовыми поля, полем даты и времени и уникальным индексом, состоящим из всех трех полей.

В этом примере создается новая таблица с двумя текстовыми полями и полем Integer. Поле SSN является первичным ключом.

Источник