Как добавить ограничение в sql

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

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

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

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

Требования

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

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

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

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

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

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

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

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

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

mysql -u 8host -p

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

CREATE DATABASE constraintsDB;

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

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

Теперь у вас есть все, чтобы следовать данному руководству. Давайте поговорим о том, как создавать ограничения и управлять ими в 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:

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

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

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

Ограничения в Microsoft SQL Server — что это такое и как их создать?

Сегодня мы с Вами затронем очень интересную, важную и, наверное, сложную для начинающих тему – это ограничения в Microsoft SQL Server. После прочтения статьи Вы узнаете, для чего нужны ограничения, какие типы ограничений бывают в SQL Server, а также научитесь создавать эти ограничения.

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

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

Ограничения – это специальные объекты в Microsoft SQL Server, с помощью которых можно задать правила допустимости определенных значений в столбцах с целью обеспечения автоматической целостности базы данных. Другими словами, ограничения формируют некое условие на те данные, которые будут вводиться в таблицу и храниться в ней. Например, какие-то данные должны быть уникальными, какие-то данные из разных таблиц должны быть неразрывно связаны по общему ключу, а какие-то данные вообще хранить не стоит, т.е. в каком-нибудь столбце не должно быть определенных значений.

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

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

В Microsoft SQL Server реализовано несколько типов ограничений, каждое из которых предназначено для выполнения какой-то конкретной задачи, и сейчас мы с Вами рассмотрим эти типы.

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

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

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

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

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

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

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

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

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

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

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

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

CHECK – это проверочное ограничение. Данное ограничение проверяет данные, на предмет выполнения определенных условий, при вводе в таблицу. Иными словами, если Вам требуется, чтобы в столбце хранились только значения, которые отвечают определённым требованиям, то как раз с помощью ограничение CHECK Вы можете автоматизировать процесс контроля за вводом данных. Например, по бизнес требованию, цена товара не должна быть отрицательной, для этого в таблице для столбца, который хранит цену товара, мы можем определить проверочное ограничение CHECK, которое будет проверять все значения, вносимые в данный столбец. Таким образом, мы на уровне сервера задаем четкие правила допустимых значений определенных столбцов.

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

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

DEFAULT – это значение по умолчанию. Мы уже говорили о том, что значение NULL — это не очень хорошо, поэтому еще одним способом избавления от данного значения, является возможность задать для столбца значение по умолчанию, которое будет сохранено, если при вводе данных мы не указали никакого значения. Например, если в столбец с ценой товара не указать цену, когда мы будет добавлять новый товар, то SQL сервер автоматически добавит значение по умолчанию, которое мы укажем при определении этого ограничения, к примеру, 0.

Примеры создания ограничений в Microsoft SQL Server

Сейчас давайте рассмотрим примеры создания и добавления ограничений на языке T-SQL. Все ограничения также можно создавать и с помощью графической среды SQL Server Management Studio.

Пример создания ограничения NOT NULL и DEFAULT

Сначала мы рассмотрим пример создавать ограничения NOT NULL и DEFAULT. Это можно сделать как при создании таблицы, так и после, т.е. добавить ограничение отдельной инструкцией.

Скриншот 1

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

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

Для второго столбца мы задали значение по умолчанию 0, с помощью ключевого слова DEFAULT, это сокращённая запись добавления данного ограничения.

Для третьего столбца мы также задали значение по умолчанию 0, но при этом использовали полное определение ограничения с применением ключевого слова CONSTRAINT (DF_C3 — это имя ограничения).

Во второй инструкции показано, как добавлять ограничение NOT NULL к существующей таблице. Как видите, это делается с помощью инструкции ALTER TABLE и команды ALTER COLUMN. Изначально столбец Column2 у нас мог принимать значение NULL, после выполнения этой инструкции не может. Однако стоит помнить о том, что, если в столбце уже будут значения NULL, инструкция не выполнится.

В третьей инструкции мы добавили к столбцу Column1 ограничение DEFAULT, для этого мы также использовали инструкцию ALTER COLUMN, а для добавления ограничения команду ADD CONSTRAINT, после которой мы написали имя ограничения (DF_C1), тип и с помощью ключевого слова FOR указали столбец, для которого мы хотим создать ограничение.

Пример создания ограничения PRIMARY KEY в Microsoft SQL Server

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

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

Скриншот 2

Первый способ подразумевает определение PRIMARY KEY на уровне столбца, т.е. мы после всех характеристик написали ключевое слово CONSTRAINT, затем название ограничения и тип этого ограничения.

Второй способ заключается в определении ограничения на уровне таблицы, иными словами, после всех столбцов мы пишем ключевое слово CONSTRAINT, имя ограничения, тип и, в данном случае, мы еще указываем какой именно столбец будет у нас выполнять роль первичного ключа (в нашем случае Column1).

Для того чтобы добавить первичный ключ к уже существующей таблице нужно использовать инструкцию ALTER TABLE и команду ADD CONSTRAINT.

Скриншот 3

В данном случае мы добавили в таблицу TestTable ограничение первичного ключа с названием PK_TestTable, Column1- это столбец, который и будет первичным ключом.

Пример создания ограничения FOREIGN KEY в SQL Server

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

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

Скриншот 4

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

В примере я также показал, что мы можем назначить некое действие в тех случаях, когда с ключом будет выполнена операция удаления или обновления. Например, категорию товара решили удалить, но это сделать не получится, если на эту запись ссылается записи из таблицы с товарами (к этой категории привязаны товары), иными словами, по умолчанию будет ошибка. Для того чтобы изменить действие по умолчанию мы можем указать в инструкции определения ограничения команды ON DELETE и ON UPDATE, т.е. соответственно действия, которые будут выполнены в случае удаления ключа, и действия, которые будут выполнены, если этот ключ будет обновлен. Возможно указать следующие значения:

CASCADE – в случае удаления будет выполнено удаление всех ссылающихся записей, т.е. если мы захотим удалить категорию мы также удалим и все товары в этой категории (SQL сервер это сделает автоматически). В случае обновления, все записи будут обновлены;
SET NULL – присвоить значение NULL, как при удалении, так и при обновлении;
SET DEFAULT — присвоить значение по умолчанию, как при удалении так и при обновлении;
NO ACTION – ничего не делать, просто будет ошибка (указывать необязательно, это действие по умолчанию).

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

Скриншот 5

К существующей таблице ограничение FOREIGN KEY добавляется так же, как и другие ограничения, инструкцией ALTER TABLE и командой ADD CONSTRAINT. В этом примере, как видите, я не указал инструкции ON DELETE и ON UPDATE, т.е. действие при удалении или обновлении будет по умолчанию.

Пример создания проверочного ограничения CHECK в MS SQL Server

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

Скриншот 6

В первом случае мы создали таблицу и сразу определили в нем проверочное ограничение CK_TestTable6_C1, которое подразумевает, что столбец Column1 не может содержать значение 0. Иными словами, если вдруг Вы или кто-то другой захочет вставить строку со значение Column1 = 0, SQL сервер не разрешит Вам это сделать, он выдаст ошибку.

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

Пример создания ограничения UNIQUE

Ограничение уникальности UNIQUE мы можем создать нескольким способами. При создании таблицы на уровне столбца, при создании таблицы на уровне таблицы, и отдельной инструкцией ALTER TABLE ADD CONSTRAINT.

Давайте посмотрим, как это делается.

Скриншот 7

В итоге мы создали таблицу, в которой три столбца и ко всем этим столбцам мы применили ограничение уникальности UNIQUE разными способами.

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

Оператор ALTER TABLE

Можно выделить следующие уровни проверочных ограничений:

уровень атрибута (столбца),
уровень кортежа (строки),
уровень отношения (таблицы).

В ограничении уровня столбца проверяется значение только одного отдельного столбца, другими словами, в ограничении данного типа имеется ссылка только на один столбец той таблицы, в определении которой содержится данное ограничение. Чтобы привести пример такого ограничения, вернёмся к схеме «Компьютерная фирма». В таблице Product в столбце type может находиться одно из трех значений. Мы можем запретить ввод любой другой информации в этот столбец при помощи такого ограничения:

Давайте сделаем отступление, чтобы познакомиться с оператором ALTER TABLE , который позволит нам изменять структуру таблицы, не пересоздавая её всякий раз заново. Это тем более важно, что изменение структуры может потребоваться тогда, когда таблица уже содержит данные.

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

В настоящий момент нас интересует добавление ограничения на столбец type , поэтому вначале приведём синтаксис оператора для добавления ограничения:

Давайте теперь добавим наше ограничение и проверим, как оно работает.

Чтобы убедиться в том, что ограничение работает как мы того ожидаем, попробуем добавить модель нового типа:

Как и ожидалось, в ответ мы получим сообщение об ошибке:

The INSERT statement conflicted with the CHECK constraint «chk_type». The conflict occurred in database «learn», table «dbo.product», column ‘type’. The statement has been terminated.

(Конфликт инструкции INSERT с ограничением CHECK «chk_type». Конфликт произошел в базе данных «learn», таблица «dbo.product», столбец ‘type’. Выполнение данной инструкции было прервано.)

Как легко догадаться, ограничение уровня строки содержит ссылки на несколько столбцов. При этом ограничение проверяется для каждой изменяемой строки отдельно. Строка может быть добавлена (или изменена), если ограничение не нарушено.

В качестве примера давайте запретим производителю Z выпускать что-либо помимо принтеров.

Итак, ограничение проверяет, что модель в таблице Product должна быть принтером производителя Z (maker=’Z’ and type= ‘printer’) или любого другого производителя (но не Z).

Ограничения в SQL

В SQL мы можем применить правила к столбцу, известные как ограничения. Эти правила относятся к данным, которые могут храниться в столбце. Например, если столбец имеет ограничение NOT NULL , то он не сможет хранить значения NULL.

В SQL используются следующие ограничения:

Ограничение	Описание
NOT NULL	Значения не могут быть NULL.
UNIQUE	Значения должны быть уникальными и не могут совпадать с уже существующими.
PRIMARY KEY	Используется для уникальной идентификации строки.
FOREIGN KEY	Ссылается на строку в другой таблице.
CHECK	Проверяет условие для вставки нового значения.
DEFAULT	Устанавливает значение по умолчанию, если оно не передано.
CREATE INDEX	Используется для ускорения процесса поиска/чтения данных.

Примечание: Эти ограничения также называются ограничениями целостности данных.