Как работать с ограничениями в SQL

Разрабатывая БД SQL, вы можете определять, какие данные можно добавлять в определенные столбцы таблицы, а какие – нет. Это возможно благодаря ограничениям. Если вы применили ограничение к столбцу или таблице, любые попытки добавить в них данные, которые не соответствуют этому правилу, завершатся неудачно.

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

Примечание: Примеры выполнены на БД MySQL.

Требования

Чтобы следовать этому руководству, вам понадобится компьютер с какой-либо реляционной системой управления базами данных (РСУБД), которая использует SQL.

Инструкции и примеры в этом руководстве были проверены в рамках следующей среды:

  • Сервер Ubuntu 20.04 с настроенным пользователем sudo и брандмауэром UFW (подобная настройка описана в этом руководстве).
  • Копия MySQL на этом сервере. Установка и защита MySQL описаны в мануале Установка MySQL в Ubuntu 20.04.

Примечание: Обратите внимание, что многие СУБД используют собственные уникальные реализации SQL. Команды, описанные в этом руководстве, будут работать в большинстве СУБД, однако в некоторых ситуациях синтаксис или вывод могут отличаться (если вы используете не MySQL, а другую СУБД).

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

Читайте также: Введение в ограничения SQL

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

Подключение и настройка тестовой базы данных

Если ваша система базы данных SQL работает на удаленном сервере, подключитесь к серверу по SSH с локального компьютера:

ssh 8host@your_server_ip

Затем откройте командную строку сервера MySQL (заменив 8host именем вашего пользователя MySQL).

mysql -u 8host -p

Создайте базу данных limitedsDB:

CREATE DATABASE constraintsDB;

Если база данных была создана успешно, вы получите такой результат:

Query OK, 1 row affected (0.01 sec)

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

USE constraintsDB;

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

Создание таблиц с ограничениями

Обычно ограничения определяются во время создания таблицы. Следующий синтаксис CREATE TABLE создает таблицу по имени employeeInfo с тремя столбцами: empId, empName и empPhoneNum. Оператор также применяет ограничение UNIQUE к столбцу empId. Данное ограничение предотвратит внесение одинаковых значений в этот столбец:

CREATE TABLE employeeInfo (
empId int UNIQUE,
empName varchar(30),
empPhoneNum int
);

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

Применить ограничение также можно вне определений столбцов. В следующем примере мы попробуем создать таблицу racersInfo с тремя столбцами: racerId, racerName и finish. После определений столбцов идет ограничение CHECK, которое применяется к столбцу finish. Благодаря этому ограничению мы можем быть уверены, что значения в данном столбце больше или равны 1 (поскольку здесь отражается порядок, в котором финишировали гонщики, и, очевидно, финишировать нулевым или минус первым невозможно):

CREATE TABLE racersInfo (
racerId int,
finish int,
racerName varchar(30),
CHECK (finish > 0)
);

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

Именование ограничений

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

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

Чтобы присвоить ограничению имя, укажите перед типом ограничения ключевое слово CONSTRAINT, за которым укажите любое имя. В примере ниже мы возвращаемся к таблице racersInfo. Переименуем таблицу в newRacersInfo и присвоим ограничению CHECK имя noNegativeFinish:

CREATE TABLE newRacersInfo (
racerId int,
finish int,
racerName varchar(30),
CONSTRAINT noNegativeFinish
CHECK (finish >= 1)
);

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

  • MySQL включает оператор SHOW CREATE TABLE, который возвращает весь оператор CREATE TABLE, создавший именованную таблицу:

SHOW CREATE TABLE имя_таблицы;

  • Клиент PostgreSQL psql имеет ряд параметров, которые можно использовать для отображения информации о заданной таблице. Параметр \d возвращает метаданные именованной таблицы:

\d имя_таблицы

Управление ограничениями

В MySQL вы можете добавлять ограничения к существующим таблицам, а также удалять их с помощью команды ALTER TABLE.

Например, следующая команда добавляет ограничение UNIQUE в столбец empName в созданной ранее таблице employeeInfo:

ALTER TABLE employeeInfo ADD UNIQUE (empName);

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

ALTER TABLE racersInfo ADD CONSTRAINT uID UNIQUE (racerId);

Если перед добавлением какого-либо ограничения вы вставили в таблицу данные, конфликтующие с новым ограничением, оператор ALTER TABLE выдаст ошибку.

Чтобы удалить ограничение, используйте синтаксис DROP CONSTRAINT, за которым укажите имя ограничения, которое хотите удалить. Следующая команда удаляет ограничение racersPK, созданное предыдущей командой:

ALTER TABLE racersInfo DROP CONSTRAINT uID;

Заключение

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

Tags: ,

Добавить комментарий