Ограничения столбцов (Constraints) в SQL

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

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

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

Основные типы ограничений в SQL ✨

В SQL существуют следующие основные типы ограничений:

PRIMARY KEY — уникальный идентификатор записи в таблице
FOREIGN KEY — обеспечивает ссылочную целостность между таблицами
UNIQUE — гарантирует уникальность значений в столбце или группе столбцов
NOT NULL — запрещает NULL-значения в столбце
CHECK — проверяет соответствие данных заданному условию
DEFAULT — устанавливает значение по умолчанию для столбца

Давайте рассмотрим каждый тип подробнее.

PRIMARY KEY (Первичный ключ)

Первичный ключ — это столбец или комбинация столбцов, которые однозначно идентифицируют каждую строку в таблице.

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

MySQL
CREATE TABLE Users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

Альтернативный синтаксис с использованием отдельного ограничения:

MySQL
CREATE TABLE Users (
    id INT,
    username VARCHAR(50),
    email VARCHAR(100),
    CONSTRAINT pk_users PRIMARY KEY (id)
);

При попытке добавить запись с уже существующим первичным ключом или с NULL-значением вместо ключа, СУБД выдаст ошибку:

MySQL
Error(1062) 23000: "Duplicate entry '1' for key 'users.PRIMARY'"

FOREIGN KEY (Внешний ключ)

Внешний ключ — это столбец или группа столбцов в одной таблице, которые ссылаются на первичный ключ другой таблицы.

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

MySQL
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES Users(id)
);

Благодаря ограничению FOREIGN KEY:

Нельзя добавить заказ для несуществующего пользователя
Нельзя удалить пользователя, у которого есть заказы (если не указаны специальные опции CASCADE)

Можно также указать действия, которые должны быть выполнены при удалении или обновлении связанных данных:

MySQL
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES Users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Возможные опции для ON DELETE и ON UPDATE:

CASCADE — автоматически удаляет или обновляет связанные записи
SET NULL — устанавливает NULL для внешнего ключа
SET DEFAULT — устанавливает значение по умолчанию
RESTRICT — запрещает удаление или обновление (используется по умолчанию)
NO ACTION — аналогично RESTRICT в большинстве СУБД

UNIQUE (Уникальность)

Ограничение UNIQUE гарантирует, что все значения в столбце или группе столбцов уникальны. В отличие от PRIMARY KEY, оно допускает NULL-значения (обычно только одно NULL-значение, так как NULL != NULL).

MySQL
CREATE TABLE Users (
    id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

Это предотвратит создание нескольких пользователей с одинаковым именем пользователя или адресом электронной почты.

NOT NULL (Запрет пустых значений)

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

MySQL
CREATE TABLE Users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    bio TEXT
);

В этом примере поля username и email обязательны, а bio может быть пустым.

CHECK (Проверка условия)

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

MySQL
CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) CHECK (price > 0),
    quantity INT CHECK (quantity >= 0)
);

Более сложный пример с именованным ограничением:

MySQL
CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birth_date DATE NOT NULL,
    hire_date DATE NOT NULL,
    CONSTRAINT chk_dates CHECK (hire_date > birth_date)
);

DEFAULT (Значение по умолчанию)

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

MySQL
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE DEFAULT CURRENT_DATE,
    status VARCHAR(20) DEFAULT 'Pending',
    FOREIGN KEY (user_id) REFERENCES Users(id)
);

В этом примере, если не указана дата заказа, будет использована текущая дата, а статус по умолчанию будет установлен как "Pending".

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

Ограничения можно добавлять не только при создании таблицы, но и при её изменении:

MySQL
-- Добавление ограничения PRIMARY KEY
ALTER TABLE Users
ADD PRIMARY KEY (id);

-- Добавление ограничения FOREIGN KEY
ALTER TABLE Orders
ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES Users(id);

-- Добавление ограничения UNIQUE
ALTER TABLE Users
ADD CONSTRAINT uq_email UNIQUE (email);

-- Добавление ограничения CHECK
ALTER TABLE Products
ADD CONSTRAINT chk_price CHECK (price > 0);

-- Добавление ограничения NOT NULL
ALTER TABLE Users
MODIFY username VARCHAR(50) NOT NULL;

-- Добавление значения по умолчанию
ALTER TABLE Orders
ALTER COLUMN status SET DEFAULT 'Pending';

Удаление ограничений также выполняется с помощью команды ALTER TABLE:

MySQL
-- Удаление ограничения PRIMARY KEY
ALTER TABLE Users
DROP PRIMARY KEY;

-- Удаление ограничения FOREIGN KEY
ALTER TABLE Orders
DROP FOREIGN KEY fk_user;

-- Удаление ограничения UNIQUE
ALTER TABLE Users
DROP CONSTRAINT uq_email;

-- Удаление ограничения CHECK
ALTER TABLE Products
DROP CONSTRAINT chk_price;

-- Удаление ограничения NOT NULL
ALTER TABLE Users
MODIFY username VARCHAR(50) NULL;

-- Удаление значения по умолчанию
ALTER TABLE Orders
ALTER COLUMN status DROP DEFAULT;

Лучшие практики использования ограничений 🚀

При проектировании базы данных следует придерживаться следующих рекомендаций:

Всегда определяйте первичный ключ для каждой таблицы, чтобы однозначно идентифицировать каждую запись.
Используйте внешние ключи для обеспечения ссылочной целостности между связанными таблицами.
Применяйте ограничение NOT NULL для столбцов, которые должны содержать значения.
Используйте ограничение UNIQUE для столбцов, которые должны содержать уникальные значения (например, email, номер телефона).
Добавляйте ограничения CHECK для столбцов, значения которых должны соответствовать определенным бизнес-правилам.
Устанавливайте значения по умолчанию для столбцов, которые часто принимают одно и то же значение.
Указывайте имена для ограничений (например, CONSTRAINT pk_users PRIMARY KEY (id)), чтобы облегчить их идентификацию и управление.

Проверка знаний о ограничениях в SQL:

Какое из следующих ограничений в SQL НЕ может содержать NULL-значения?

Telegram