SQL — Ограничения
От автора: ограничения SQL — это правила, применяемые к столбцам данных таблицы. Они используются, чтобы ограничить типы данных, которые могут храниться в таблице. Это обеспечивает точность и надежность данных в базе данных.
Ограничения могут применяться либо на уровне столбцов, либо на уровне таблицы. Ограничения на уровне столбца применяются только к одному столбцу, тогда как ограничения уровне таблицы применяются ко всей таблице.
Ниже приведены некоторые из наиболее часто используемых ограничений, доступных в SQL. Эти ограничения уже рассматривались в главе «Концепции SQL-RDBMS» , но сейчас мы вернемся к ним еще раз.
NOT NULL Constraint — столбец не может иметь значение NULL.
DEFAULT Constraint — задает значение по умолчанию для столбца, если оно не указано.
UNIQUE Constraint — все значения в столбце должны быть разными.
PRIMARY Key — уникальная идентификация каждой строки/записи в таблице базы данных.
FOREIGN Key — уникально идентифицирует строку/запись в любой другой таблице базы данных.
CHECK Constraint — ограничение CHECK обеспечивает, чтобы все значения в столбце удовлетворяли определенным условиям.
INDEX — используется для быстрого создания данных базы данных.
Ограничения могут указываться при создании таблицы с помощью оператора CREATE TABLE или вы можете использовать оператор ALTER TABLE для создания ограничений уже после создания таблицы.
Удаление ограничений
Любое ограничение, которое вы определили, можно удалить с помощью команды ALTER TABLE с параметром DROP CONSTRAINT.
Например, чтобы удалить ограничение первичного ключа в таблице EMPLOYEES, вы можете использовать следующую команду.
Предложение 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 имя
первичный_ключ1[, первичный_ключ2 [, . ]]) |
UNIQUE (уникальный_ключ1[, уникальный_ключ2 [, . ]]) |
NOT NULL (непустое1[, непустое2 [, . ]]) |
FOREIGN KEY [NO INDEX] (ссылка1[, ссылка2 [, . ]]) REFERENCES внешняя_таблица [(внешнее_поле1 [, внешнее_поле2 [, . ]])]
[ON UPDATE CASCADE | SET NULL]
[ON DELETE CASCADE | SET NULL]>
Предложение CONSTRAINT включает в себя следующие элементы:
Имя создаваемого ограничения.
первичный_ключ1, первичный_ключ2
Имена полей, определяемых как первичный ключ.
уникальный_ключ1, уникальный_ключ2
Имена полей, определяемых как уникальный ключ.
непустое1, непустое2
Имена полей, в которых не может быть значений NULL.
ссылка1, ссылка2
Имя поля или полей внешнего ключа, которые ссылаются на поля в другой таблице.
внешняя_таблица
Имя поля внешняя таблица которое содержит поле или поля, заданные полем внешнего поля.
внешнее_поле1, внешнее_поле2
Имена полей во внешней_таблице, заданных аргументами ссылка1, ссылка2. Если ссылка указывает на поле первичного ключа внешней_таблицы, данный аргумент можно опустить.
Замечания
Выражение для ограничения на одно поле указывается в предложении определения поля инструкции ALTER TABLE или CREATE TABLE непосредственно за спецификацией типа данных поля.
Выражение для ограничения на несколько полей указывается тогда, когда зарезервированное слово CONSTRAINT используется вне предложения определения поля в инструкции ALTER TABLE или CREATE TABLE.
В предложении CONSTRAINT можно задать для поля один из следующих типов ограничений:
- Зарезервированное слово UNIQUE используется для назначения поля в качестве уникального ключа. Это значит, что две записи в таблице не могут иметь одно и то же значение в этом поле. Любое поле (или список полей) можно ограничить как уникальное. Если ограничение на несколько полей назначено уникальным ключом, объединенные значения всех полей в индексе должны быть уникальными, даже если несколько записей имеют одинаковое значение в одном из полей.
- Зарезервированные слова PRIMARY KEY используются для назначения одного поля или совокупности полей таблицы в качестве первичного ключа. Все значения в первичном ключе должны быть уникальными и отличными от NULL, причем в таблице может быть только один первичный ключ.
Примечание: Ограничение PRIMARY KEY не следует устанавливать в таблице, уже имеющей первичный ключ: это приведет к ошибке.
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. Определение внешнего ключа в такой форме должно применяться только в том случае, если получающиеся в результате значения индексов будут часто повторяться. Если значения в индексе внешнего ключа часто повторяются, использование индекса может быть менее эффективно, чем простое сканирование таблицы. Сохранение такого индекса при вставке и удалении строк таблицы снижает производительность и не приносит никакой пользы.
Ограничения — Основы реляционных баз данных
Первичные и внешние ключи в базах данных относятся к ограничению. Оно включает в себя различные модификаторы колонок в базе данных, которые добавляют ограничения на их содержимое. В этом уроке мы рассмотрим уже известные ограничения, а также изучим новые.
Первичный ключ
Первичный ключ однозначно идентифицирует каждую запись внутри таблицы. Задается с помощью фразы PRIMARY KEY , которая добавляется после указания типа, когда создается таблица. Первичный ключ в таблице может быть только один, и для него используется суррогатный ключ — идентификатор, у которого нет физического смысла:
CREATE TABLE products ( id bigint PRIMARY KEY, name text, price numeric ); Внешний ключ
Внешний ключ — это ограничение, которое связывает указанную колонку с данными из другой таблицы. Такое ограничение гарантирует целостность данных. То есть попытка удалить данные, у которых есть зависимые данные, приведет к ошибке:
CREATE TABLE orders ( id bigint PRIMARY KEY, product_id bigint REFERENCES products (id), quantity integer ); В данной таблице атрибут product_id связан с атрибутом id таблицы products . Синтаксически для этого добавляется ключевое слово REFERENCES после типа поля. Затем добавляется имя таблицы, на которую идет связь. В скобках указывается поле, с которым эта связь создается. Наличие такой связи проявляется следующим образом:
- Если попробовать вставить в orders запись со значением product_id , которого нет в таблице products , то возникнет ошибка
- Если попытаться удалить запись из products , на которую есть ссылки из таблицы orders , то возникнет ошибка
Внешний ключ проверяет целостность данных и не позволяет появляться «мусору». Поэтому лучше ставить внешние ключи на все идентификаторы, для которых есть родительская таблица в базе.
Внешние ключи поддерживают несколько вариаций. Таким образом они поддерживают целостность, но не приводят к ошибкам при попытке удалить запись.
Вариант 1 — Каскадное удаление:
CREATE TABLE orders ( product_id bigint REFERENCES products (id) ON DELETE CASCADE, ); В таком варианте при попытке удалить родительскую запись (products) будут удалены все записи в orders, которые ссылаются на этот продукт. Каскадное удаление — опасная опция, так как можно случайно лишиться данных. Стоит хорошо подумать, прежде чем включать ее.
Вариант 2 — Записи остаются, но вместо идентификатора проставляется null :
CREATE TABLE orders ( product_id bigint REFERENCES products (id) ON DELETE SET NULL ); Используется нечасто, но иногда может быть полезно.
Not Null
Not Null — это ограничение указывает на то, что колонка не может содержать null значения. Его можно комбинировать с другими ограничениями, например, внешними ключами. Первичный ключ является NOT NULL по умолчанию:
CREATE TABLE products ( id bigint PRIMARY KEY, name text NOT NULL, price numeric ); CREATE TABLE orders ( id bigint PRIMARY KEY, product_id bigint REFERENCES products (id) NOT NULL, quantity integer ); Уникальность
Чтобы гарантировать уникальность значений одного поля, используется ключевое слово UNIQUE . Обычно его используют в описании конкретного поля, когда создается таблица:
CREATE TABLE products ( id bigint PRIMARY KEY, name text UNIQUE, price numeric ); UNIQUE никогда не используется совместно с первичным ключом, так как он уникален по определению.
Иногда бывает нужно реализовать уникальность по двум или более атрибутам. Для этого нужно описать UNIQUE отдельно от конкретного поля:
CREATE TABLE products ( id bigint PRIMARY KEY, name text, locale varchar, price numeric, UNIQUE(name, locale) ); Существует ситуация, в которой ограничение уникальности не срабатывает. Такое происходит, если хотя бы в одном из столбцов, включенных в ограничение, встречается NULL . По стандарту, NULL никогда не равен самому себе.
Открыть доступ
Курсы программирования для новичков и опытных разработчиков. Начните обучение бесплатно
- 130 курсов, 2000+ часов теории
- 1000 практических заданий в браузере
- 360 000 студентов
Наши выпускники работают в компаниях:
Что означает CONSTRAINT в данном контексте?
Скрипт писал не я, и пытаюсь понять логику человека который это делал. В чем может быть смысл этого ограничения CONSTRAINT person_pkey PRIMARY KEY (id) ? И во втором случае аналогично. Это ограничение что id может быть только первичным ключем ? А зачем это может быть нужно? Тем более еще и именовать это ограничение. Помогите пожалуйста разобраться зачем делать такой CONSTRAINT ?
Отслеживать
18.5k 4 4 золотых знака 31 31 серебряный знак 45 45 бронзовых знаков
задан 11 мая 2017 в 19:47
5,297 11 11 золотых знаков 58 58 серебряных знаков 116 116 бронзовых знаков
Для определения первичного ключа, т.е. нельзя будет добавить два одинаковых или неопределённых id. Или другими словами для уникальности энтити, ну типа был Пётр 1-й потом Пётр 3-й, два разных мужика.
11 мая 2017 в 19:55
1 ответ 1
Сортировка: Сброс на вариант по умолчанию
CONSTRAINT company_pkey PRIMARY KEY (id) PRIMARY KEY (id) и означает, что id является первичным ключом таблицы.
Т.к. в данном случае первичный ключ состоит из одного столбца, то его можно было бы указать на уровне поля:
CREATE TABLE company( id integer PRIMARY KEY, name character varying ); Возможность определения ключа на уровне таблицы полезна если ключ — составной
PRIMARY KEY (id, name) В первом случае у ограничения задано имя. Это имя будет выводиться в сообщениях об ошибках. Также по имени можно это ограничение удалить. В случае если имя ограничения не задано явно, оно будет сгенерировано СУБД.
CONSTRAINT constraint_name
An optional name for a column or table constraint. If the constraint is violated, the constraint name is present in error messages, so constraint names like col must be positive can be used to communicate helpful constraint information to client applications. (Double-quotes are needed to specify constraint names that contain spaces.) If a constraint name is not specified, the system generates a name.