Ограничения SQL: как создать, примеры

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

dbaas

Что такое ограничения таблицы SQL

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

Добавление ограничений SQL

Создать ограничения SQL можно, используя инструкции PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK и NOT NULL.

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

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

CREATE TABLE Countries (
Country VARCHAR(46) NOT NULL,
Capital VARCHAR(46)
)

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

INSERT INTO Countries VALUES (null, 'Madrid')

Результатом будет эта ошибка:

Column 'Country' cannot be null

А вот такая запись ошибки не вызовет, потому что оставлять пустым столбец с названиями столиц (Capital) мы не запрещали:

INSERT INTO Countries VALUES ('Spain', null)

Ограничение NOT NULL может быть полезно для столбцов с контактными данными, когда нам нужно обязать пользователя, например, ввести свою электронную почту или номер телефона. Поэтому такие обязательные поля нередко используют ограничение NOT NULL, чтобы гарантировать, что пользователь введет определенное значение:

CREATE TABLE Subscribers (
SubscriberName VARCHAR(46) NOT NULL,
SubscriberContact VARCHAR(46) NOT NULL,
)

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

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

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

CREATE TABLE Workers1 (
WorkerName VARCHAR(46) NOT NULL,
WorkerDate DATE,
WorkerContact INTEGER UNIQUE
)

Мы создали таблицу работников, в которую будем добавлять имя работника (поле не может быть пустым, так как мы установили для него уже знакомое ограничение NOT NULL), дату приема на работу (в формате даты, на что указывает тип данных DATE) и номер телефона. При этом номер телефона должен быть уникальным, на что и указывает ограничение UNIQUE. Давайте вставим в нашу таблицу следующие данные:

INSERT INTO Workers1 VALUES ('Vasya Pupkin', DATE '2018-05-10', 89009000000)

Теперь при попытке добавления строки с таким же номером телефона:

INSERT INTO Workers1 VALUES ('Petya Pupkin', DATE '2020-06-11', 89009000000)

Программа выдаст ошибку:

Duplicate entry '89009000000' for key 'uniqueconstraint.WorkerContact'

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

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

Check в переводе с английского значит «проверять», и ограничение CHECK служит для проверки значений по определенному условию. Рассмотрим следующий пример:

CREATE TABLE Customers1 (
CustomerName1 VARCHAR(46),
CustomerName2 VARCHAR(46),
CustomerEmail VARCHAR(56),
CustomerAge INTEGER CHECK (CustomerAge>17)
)

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

INSERT INTO Customers1 VALUES ('Vasya', 'Pupkin', 'vasya_pupkin@anysite.com', 17)

Вот что нам выдаст система:

Check constraint 'checkconstraint_chk_1' is violated

Инструкцию CHECK можно использовать для реализации пользовательских ограничений. Так, если в таблице должны храниться только данные взрослых, мы могли бы использовать ограничение CHECK для столбца «Возраст покупателя» (CustomerAge>17, как в примере выше). Другой пример: если в таблице должны храниться данные только граждан России, мы могли бы использовать CHECK: например, для нового столбца CustomerCountry: CHECK (CustomerCountry='Russia').

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

PRIMARY KEY — это одно из ограничений ключа таблицы SQL, в данном случае — первичного. PRIMARY KEY используется для создания идентификатора, с которым соотносится каждая строка в таблице. Добавим, что PRIMARY KEY в таблице может относиться только к одному столбцу (и это понятно, так как это идентификатор). Соответственно, каждое значение PRIMARY KEY обязательно должно быть уникальным, при этом нулевые значения в столбце, определенном с помощью PRIMARY KEY, не допускаются. Чтобы было понятнее, о чём речь, рассмотрим следующий пример:

CREATE TABLE Workers2 (
id INTEGER PRIMARY KEY,
WorkerName1 VARCHAR(46),
WorkerName2 VARCHAR(46),
WorkerAge INTEGER CHECK (WorkerAge>17)
)

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

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

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

CREATE TABLE WorkersTaxes (
WorkerTax INTEGER,
Worker_id INTEGER,
FOREIGN KEY (Worker_id) REFERENCES Workers2(id)
)

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

Подготовили для вас выгодные тарифы на DBaaS

Другие ограничения

Осталось добавить, что к ограничениям SQL Standard также иногда относят DEFAULT, однако DEFAULT не ограничивает тип вводимых данных, поэтому технически не может быть отнесен к ограничениям. Тем не менее эту инструкцию также следует упомянуть здесь, поскольку она позволяет реализовать довольно важную функцию: подстановку значений по умолчанию, когда пользователь их не вводит. Это может понадобится, например, для того, чтобы избежать возможных ошибок при отсутствии ввода. Рассмотрим следующий пример:

CREATE TABLE Customers2 (
CustomerName1 VARCHAR(46) NOT NULL,
CustomerName2 VARCHAR(46) NOT NULL,
CustomerAge INTEGER DEFAULT 18,
)

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

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