Основы работы с ограничениями SQL

MariaDB, mySQL | Amber | Комментировать запись

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

Реляционные системы управления базами данных (РСУБД) позволяют контролировать данные, помещаемые в таблицу. Этот контроль выполняется при помощи ограничений. В контексте РСУБД ограничение – это специальное правило, которое применяется к одному или нескольким столбцам (иногда и ко всей таблице) и определяет, какие изменения могут быть внесены в данные с помощью операторов INSERT, UPDATE или DELETE.

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

Что такое ограничения SQL?

В SQL ограничение – это любое правило, применяемое к столбцу или таблице, которое определяет, какие данные можно в него вносить, а какие – нет. Каждый раз, когда вы пытаетесь выполнить операцию, изменяющую данные в таблице, – такую ​​как INSERT, UPDATE или DELETE, – СУБД проверяет, не нарушают ли эти данные какие-либо существующие ограничения. Если ограничения запрещают выполнять такую операцию, она возвращает ошибку.

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

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

Кроме того, ограничения также помогают поддерживать целостность данных. Целостность данных – это широкий термин, который обычно используется для описания точности, согласованности и рациональности данных БД в зависимости от конкретного случая использования. Таблицы в БД часто тесно связаны, при этом столбцы в одной таблице зависят от значений в другой. При вводе данных часто есть риск человеческой ошибки, и здесь на помощь приходят ограничения: благодаря им неправильно введенные данные не смогут повлиять на отношения внутри БД и, следовательно, не смогут повредить целостность данных.

Представьте, что вы создаете базу данных с двумя таблицами: одна для перечисления текущих учеников школы, а другая – для перечисления членов баскетбольной команды этой школы. В таком случае вам бы пригодилось ограничение FOREIGN KEY. Если применить его к столбцу в таблице баскетбольной команды, который ссылается на столбец в общей школьной таблице, вы установите между двумя таблицами связь: любая запись в таблице баскетбольной команды должна обязательно ссылаться на существующую запись в общей таблице учеников.

Ограничения определяются при создании таблицы или могут быть добавлены позже с помощью оператора ALTER TABLE (при этом ограничения не должны конфликтовать с данными, уже находящимися в таблице). Создавая ограничение, СУБД автоматически генерирует его имя, но в большинстве реализаций SQL вы можете самостоятельно выбрать имя для любого ограничения. Эти имена используются для обозначения ограничений в операторах ALTER TABLE при их изменении или удалении.

Стандарт SQL формально определяет всего пять ограничений:

• PRIMARY KEY
• FOREIGN KEY
• UNIQUE
• CHECK
• NOT NULL

Примечание: Многие СУБД также поддерживают ключевое слово DEFAULT, которое используется для определения ненулевого стандартного значения для столбца на случай, если значение не указано при вставке строки. В документации некоторых из этих СУБД ключ DEFAULT упоминается как ограничение, поскольку их реализации SQL используют синтаксис DEFAULT по аналогии с синтаксисом таких ограничений, как UNIQUE или CHECK. Однако технически DEFAULT не является ограничением, поскольку не ограничивает, какие данные могут быть введены в столбец.

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

Ограничение PRIMARY KEY

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

В контексте реляционной модели ключ – это столбец или набор столбцов в таблице, в которой каждое значение гарантированно уникально и не содержит значений NULL. Первичный ключ (primary key) – это специальный ключ, значения которого используются для идентификации отдельных строк в таблице; столбец или столбцы, представляющие собой первичный ключ, могут использоваться для идентификации во всех остальных таблицах БД.

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

Вы можете создать первичный ключ в SQL с помощью ограничения PRIMARY KEY, которое по сути представляет собой комбинацию ограничений UNIQUE и NOT NULL. После определения первичного ключа СУБД автоматически создаст связанный с ним индекс. Индекс – это структура БД, которая помогает быстрее извлекать данные из таблицы (она работает подобно указателю в учебнике). Запросы просматривают только записи из проиндексированного столбца, чтобы найти связанные значения. Таким образом, первичный ключ действует как идентификатор для каждой строки в таблице.

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

• studentID: используется для хранения уникального идентификационного номера каждого учащегося.
• firstName: хранит имя каждого учащегося.
• lastName: содержит фамилию каждого ученика.

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

Первичный ключ, состоящий из всех трех столбцов, тоже может существовать, но такой вариант будет избыточным: сам по себе идентификационный номер каждого ученика уже уникален, а значит, имя или фамилия тут будут лишними. Лучше всего выбрать минимальный набор атрибутов, который может идентифицировать каждую строку, это и будет первичным ключом таблицы, – в нашем примере вполне достаточно данных из столбца studentID.

Если ключ состоит из данных, которые представляют сущности, события или атрибуты реального мира, он называется естественным ключом (или natural key). Ключ, который создается внутри базы данных и не представляет ничего за ее пределами, называется суррогатным или синтетическим ключом (surrogate key). Некоторые СУБД не рекомендуют использовать естественные ключи, поскольку даже кажущиеся постоянными точки данных могут изменяться непредсказуемым образом.

Ограничение FOREIGN KEY

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

Если у вас есть две таблицы, которые вы хотите связать друг с другом, то определить внешний ключ FOREIGN KEY – один из способов сделать это. Внешний ключ – это столбец в одной (дочерней) таблице, значения которого берутся из столбца в другой (родительской) таблице. Это способ выразить связь между двумя таблицами: ограничение FOREIGN KEY требует, чтобы значения в столбце, к которому оно применяется, уже существовали в столбце, на который оно ссылается.

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

Часто внешний ключ дочерней таблицы является первичным ключом родительской таблицы, но это не всегда так. В большинстве СУБД внешний ключ дочерней таблицы может ссылаться на любой столбец родительской таблицы, к которому применено ограничение UNIQUE или PRIMARY KEY.

Ограничение UNIQUE

Ограничение UNIQUE запрещает добавлять в заданный столбец любые повторяющиеся значения.

Как следует из названия, ограничение UNIQUE требует, чтобы каждая запись в данном столбце была уникальной. Любая попытка добавить значение, которое уже существует в этом столбце, приведет к ошибке.

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

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

Читайте также: Создаем отношение «один ко многим» в Laravel Eloquent

Добавляя ограничение UNIQUE к столбцу, к которому было применено ограничение FOREIGN KEY, вы можете установить между таблицами связь «один к одному»:, каждая запись в родительской таблице появляется в дочерней таблице только один раз.

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

Ограничение CHECK

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

Предикаты CHECK записываются в форме выражения, которое может иметь значение TRUE, FALSE или потенциально UNKNOWN. Если вы попытаетесь ввести в столбец с ограничением CHECK какое-то значение и предикат оценит его как TRUE или UNKNOWN (для значений NULL), операция завершится успешно. Однако, если выражение получит оценку FALSE, оно выдаст ошибку.

Часто для ограничения диапазона данных, разрешенных в данном столбце, предикаты CHECK полагаются на математический оператор сравнения (например, < или >, <= или >=). Например, CHECK часто используется для того, чтобы запретить отрицательные значения в некоторых столбцах (это полезно в тех случаях, когда отрицательное значение не имеет смысла, как вы увидите в следующем примере).

Следующий оператор CREATE TABLE создает таблицу productInfo со столбцами для наименования каждого продукта, его идентификационного номера и цены. Цена не может быть выражена отрицательным числом, потому этот оператор накладывает ограничение CHECK на столбец price, чтобы он содержал только положительные значения:

CREATE TABLE productInfo (
productID int,
name varchar(30),
price decimal(4,2)
CHECK (price > 0)
);

Не каждый предикат CHECK должен использовать математический оператор сравнения. Как правило, вы можете использовать любой оператор SQL, который может оцениваться как TRUE, FALSE или UNKNOWN, в том числе LIKE, BETWEEN, IS NOT NULL и другие. Некоторые реализации SQL (но не все!) даже позволяют включать подзапрос в предикат CHECK. Однако имейте в виду, что большинство реализаций все же не позволяют ссылаться на другую таблицу в предикате.

Ограничение NOT NULL

NOT NULL запрещает добавлять в данный столбец значения NULL.

В большинстве реализаций SQL СУБД по умолчанию представляет отсутствующие данные как NULL, если вы добавляете строку данных, но не указываете значение для определенного столбца. NULL в SQL – это специальное ключевое слово, используемое для представления неизвестного, отсутствующего или иного неуказанного значения. Однако NULL – это не само значение, а состояние неизвестного значения.

Попробуем проиллюстрировать эту разницу. Представьте себе таблицу для отслеживания клиентов в кадровом агентстве, в которой есть столбцы для имени и фамилии каждого клиента. Если клиент использует мононим, – как, например, Cher или Beyoncé, – администратор базы данных может ввести его только в столбец для имени, в результате чего СУБД введет NULL в столбец фамилии. Конечно, база данных не считает, что фамилия клиента буквально равна нулю. Это просто означает, что значение данного столбца в этой строке неизвестно или это поле не применяется для этой конкретной записи.

Из названия видно, что ограничение NOT NULL не позволяет хранить значения NULL в данном столбце. Значит, при вставке новой строки вы должны указать какое-то значение для столбца с ограничением NOT NULL. В противном случае операция INSERT завершится ошибкой.

Заключение

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