Разрабатывая БД 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 использует свою собственную уникальную реализацию языка. Для получения более полного и точного описания каждой команды и полного набора их параметров вам следует обратиться к официальной документации вашей СУБД.