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

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

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

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

Например, мы храним данные о сотрудниках в таблице Employees, и нам нужно не допустить сохранения отрицательных значений в столбце с возрастом. С помощью ограничения CHECK мы можем задать условие «Age > 0», и все вводимые данные, не соответствующие этому условию, не будут сохранены.

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

Есть два вида ограничений: ограничения целостности данных и ограничения целостности ссылок.

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

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

К целостным ограничениям относятся:

— Ограничение NOT NULL. Гарантирует отсутствие пустых значений (NULL) в столбце.

— UNIQUE — ограничение, которое гарантирует уникальность значений.

— PRIMARY KEY constraint (ограничение первичного ключа). Уникально идентифицирует каждую запись в таблице.

Ограничения целостности классифицируются по области действия, способам реализации и времени проверки.

Ограничения целостности по области действия

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

Ограничения целостности по времени проверки

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

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

Рассмотрим способы создания ограничений в базах данных.

Ограничение Not Null

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

Предположим, нам нужно создать таблицу Employees со столбцами EmployeeID, FirstName, LastName, Age и Email. Используем ограничение NOT NULL, чтобы не допустить пустых значений в столбцах FirstName, LastName, Age и Email. Команда будет выглядеть следующим образом:

/* Создание таблицы Employees

Где:
— EmployeeID —  уникальный идентификатор сотрудника,
— FirstName — столбец с типом данных «строка» (ячейка объемом до 50 символов), который будет хранить имена сотрудников,
— LastName — столбец с типом данных «строка» (ячейка объемом до 50 символов), который будет хранить фамилии сотрудников, 
— Email —  столбец с типом данных «строка» (ячейка объемом до 100 символов), который хранит email сотрудников,
— Age — столбец с возрастом сотрудников. */

CREATE TABLE Employees (
 EmployeeID INT PRIMARY KEY,
 FirstName VARCHAR(50) NOT NULL,
 LastName VARCHAR(50) NOT NULL,
 Age INT NOT NULL,
 Email VARCHAR(100) NOT NULL
);

Ограничение уникальности

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

/* Создание таблицы Employees с ограничением уникальности и NOT NULL.

Где:
— EmployeeID —  уникальный идентификатор сотрудника,
— FirstName — столбец с типом данных «строка» (ячейка объемом до 50 символов), который будет хранить имена сотрудников,
— LastName — столбец с типом данных «строка» (ячейка объемом до 50 символов), который будет хранить фамилии сотрудников, 
— Email —  столбец с типом данных «строка» (ячейка объемом до 100 символов), который хранит email сотрудников,
— Age — столбец, который будет хранить возраст сотрудников. */

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Age INT,
    Email VARCHAR(100) UNIQUE NOT NULL 
);

Primary Key Constraint

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

Создадим еще одну таблицу Departments со столбцами DepartmentID и DepartmentName:

/* Создание таблицы Departments с первичным ключом в столбце DepartmentID.

Где:
— DepartmentID —  уникальный идентификатор отдела,
— DepartmentName — столбец с названиями отделов и типом данных  «строка» (ячейка объемом до 50 символов). */

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50)
);

Foreign Key Constraint

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

Родительская таблица — это таблица, которая содержит первичный ключ или уникальный идентификатор каждой записи. Другие таблицы, связанные с ней, ссылаются на ее первичный ключ через внешний.

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

Создадим связь между таблицами Employees (дочерняя) и Departments (родительская):

CREATE TABLE Employees (

    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Age INT,
    Email VARCHAR(100),
    DepartmentID INT, 
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

Где:

— DepartmentID — столбец, который будет хранить идентификаторы отделов, к которым прикреплены сотрудники,

— FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) — запрос для определения внешнего ключа.

Значения в столбце DepartmentID в таблице Employees будут ссылаться на существующие значения в столбце DepartmentID таблицы Departments. То есть каждый отдел в таблице Employees должен существовать в таблице Departments.

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

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

CREATE TABLE Employees (

 EmployeeID INT PRIMARY KEY,
 FirstName VARCHAR(50),
 LastName VARCHAR(50),
 Age INT CHECK (Age >= 18)
);

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

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

Пример использования ограничения:

/* Создание таблицы Employees.

Где:
— EmployeeID —  уникальный идентификатор сотрудника,
— FirstName — столбец с типом данных «строка» (ячейка объемом до 50 символов), который будет хранить имена сотрудников,
— LastName — столбец с типом данных «строка» (ячейка объемом до 50 символов), который будет хранить фамилии сотрудников, 
— Email —  столбец с типом данных «строка» (ячейка объемом до 100 символов), который хранит email сотрудников,
— Age — столбец, который будет хранить возраст сотрудников. */

CREATE TABLE Employees (
 EmployeeID INT PRIMARY KEY,
 FirstName VARCHAR(50),
 LastName VARCHAR(50),
 Email VARCHAR(100) DEFAULT 'test@reg.ru,
 Age INT DEFAULT 30
);

Если при вставке новой строки не указать значения в столбцах Email и Age, они будут заполнены автоматически: вместо email будет указан стандартный test@reg.ru, а возраст будет равен 30.