Раздел 34 · MySQL с нуля

Ограничения: NOT NULL, UNIQUE, DEFAULT, CHECK

junior~25 мин

без входаВойди, чтобы отмечать прогресс, вести заметки и решать задачи в редакторе. войти

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

Анкета с обязательными полями

Вспомни анкету в банке. У некоторых полей звёздочка: без фамилии анкету не примут. В поле «гражданство» уже напечатано «РФ», если не зачеркнёшь, так и останется. Номер паспорта сверят с базой: второй раз тот же номер не пройдёт. А в поле «возраст» не напишешь 250, сотрудник вернёт анкету.

У таблицы те же четыре правила, их называют ограничениями:

Поле анкетыОграничениеЧто делает
звёздочкаNOT NULLзначение обязательно
напечатанное «РФ»DEFAULTзначение, если его не дали
сверка номераUNIQUEповтор запрещён
«возраст от 0 до 120»CHECKусловие на значение

Строгий режим

Прежде чем смотреть на ограничения, проверим, как настроен сервер:

SELECT @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Главное слово здесь STRICT_TRANS_TABLES, строгий режим. Он включён по умолчанию с MySQL 5.7. Без него многие нарушения не ломали бы запрос: MySQL молча подставил бы пустую строку, ноль или обрезанное значение и выдал предупреждение, которое никто не читает. В старых проектах строгий режим иногда выключен, и все примеры ниже там ведут себя иначе.

NOT NULL: обязательное поле

В таблице viewers у колонок name, email и registered_at стоит NOT NULL. Забудем дату регистрации:

INSERT INTO viewers (name, email) VALUES ('Жанна', 'zhanna@example.com');
ERROR 1364 (HY000): Field 'registered_at' doesn't have a default value

Колонку мы не упомянули, значения по умолчанию у неё нет, а пустой быть она не может. Ошибка 1364. Если написать NULL явно, ошибка другая:

INSERT INTO viewers (name, email, registered_at) VALUES (NULL, 'zhanna@example.com', '2026-10-24');
ERROR 1048 (23000): Column 'name' cannot be null

Какие колонки ставить NOT NULL? Те, без которых строка теряет смысл. Зритель без имени и email никому не нужен, а телефон и город могут быть неизвестны, поэтому в kino у них NULL разрешён. Что NULL означает и почему с ним осторожно, разобрано в уроке про NULL и целостность.

UNIQUE: без повторов

У email стоит UNIQUE: двух зрителей с одним адресом быть не может. Анна уже зарегистрирована как anna@example.com. Попробуем завести ещё один аккаунт, набрав адрес большими буквами:

INSERT INTO viewers (name, email, registered_at) VALUES ('Жанна', 'ANNA@example.com', '2026-10-24');
ERROR 1062 (23000): Duplicate entry 'ANNA@example.com' for key 'viewers.email'

ANNA@example.com для MySQL дубль anna@example.com. Правила сравнения по умолчанию не различают регистр, мы встречали это в уроке про строки и с первичным ключом в уроке про CREATE TABLE. Для email это то, что нужно: почтовые сервисы почти всегда не различают регистр в адресе. Но если хранишь в UNIQUE что-то, где Abc и abc разные значения, например коды с учётом регистра, это ловушка.

А как UNIQUE относится к пустым значениям? Телефон у зрителя необязателен, но если он есть, второго такого быть не должно. Проверим на отдельной таблице:

CREATE TABLE unique_null (phone VARCHAR(20) UNIQUE);
INSERT INTO unique_null VALUES (NULL), (NULL), ('+79161234567');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
INSERT INTO unique_null VALUES ('+79161234567');
ERROR 1062 (23000): Duplicate entry '+79161234567' for key 'unique_null.phone'

Два NULL прошли, два одинаковых номера нет. NULL значит «неизвестно», а два неизвестных значения не считаются равными. Так UNIQUE и задуман: пустых сколько угодно, заполненные без повторов.

Теперь вставим Жанну правильно и спросим номер новой строки:

INSERT INTO viewers (name, email, registered_at) VALUES ('Жанна', 'zhanna@example.com', '2026-10-24');
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                8 |
+------------------+
1 row in set (0.00 sec)

Почему 8, а не 7? Посмотри на список:

SELECT id, name, email FROM viewers WHERE id > 5;
+----+---------------------+--------------------+
| id | name                | email              |
+----+---------------------+--------------------+
|  6 | Егор Ёлкин          | egor@example.com   |
|  8 | Жанна               | zhanna@example.com |
+----+---------------------+--------------------+
2 rows in set (0.00 sec)

Номер 7 съела неудачная вставка с дублем email. MySQL выдал номер до того, как проверил UNIQUE, а возвращать номера счётчик не умеет. Две первые ошибки, 1364 и 1048, номер не тронули: строку отбросили раньше. Дыры в id нормальны, не пытайся их закрывать. Подробнее о счётчике в уроке про AUTO_INCREMENT.

DEFAULT и CHECK на новой таблице

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

CREATE TABLE gift_cards (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code CHAR(8) NOT NULL UNIQUE,
  amount DECIMAL(8, 2) NOT NULL DEFAULT 1000.00,
  status ENUM('active', 'used') NOT NULL DEFAULT 'active',
  issued_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (amount > 0),
  CONSTRAINT chk_gift_amount_max CHECK (amount <= 50000)
);
Query OK, 0 rows affected (0.00 sec)

DEFAULT задаёт значение для случая, когда колонку не упомянули: карта по умолчанию на 1000 рублей, активна, выпущена сейчас. CHECK проверяет условие. Первую проверку мы оставили без имени, второй дали имя через CONSTRAINT. Значение по умолчанию не обязано быть константой: у issued_at это CURRENT_TIMESTAMP из урока про дату и время, и MySQL вычисляет его заново при каждой вставке. Так у каждой карты своё время выпуска, хотя в запросе его никто не писал.

Карта по умолчанию:

INSERT INTO gift_cards (code) VALUES ('GIFT0001');
Query OK, 1 row affected (0.00 sec)

Нулевая и слишком большая сумма:

INSERT INTO gift_cards (code, amount) VALUES ('GIFT0002', 0);
ERROR 3819 (HY000): Check constraint 'gift_cards_chk_1' is violated.
INSERT INTO gift_cards (code, amount) VALUES ('GIFT0003', 70000);
ERROR 3819 (HY000): Check constraint 'chk_gift_amount_max' is violated.

Сравни сообщения. Безымянную проверку MySQL назвал gift_cards_chk_1: таблица, _chk_, номер. По такому имени в журнале ошибок не поймёшь, что сломалось. Имя chk_gift_amount_max говорит само за себя. Давай проверкам имена.

DEFAULT не спасает от явного NULL: если написал NULL, значит, хотел NULL, и NOT NULL его не пустит. А чтобы взять значение по умолчанию явно, есть слово DEFAULT:

INSERT INTO gift_cards (code, amount) VALUES ('GIFT0004', NULL);
ERROR 1048 (23000): Column 'amount' cannot be null
INSERT INTO gift_cards (code, amount, status) VALUES ('GIFT0005', 500, DEFAULT);
SELECT * FROM gift_cards;
+----+----------+---------+--------+---------------------+
| id | code     | amount  | status | issued_at           |
+----+----------+---------+--------+---------------------+
|  1 | GIFT0001 | 1000.00 | active | 2026-10-24 12:00:00 |
|  2 | GIFT0005 |  500.00 | active | 2026-10-24 12:00:00 |
+----+----------+---------+--------+---------------------+
2 rows in set (0.00 sec)

Вторая карта получила id 2: проваленные CHECK и NOT NULL номеров не съели, в отличие от дубля UNIQUE.

Где увидеть ограничения

DESCRIBE показывает NOT NULL, UNIQUE и DEFAULT, но про CHECK молчит:

DESCRIBE gift_cards;
+-----------+-----------------------+------+-----+-------------------+-------------------+
| Field     | Type                  | Null | Key | Default           | Extra             |
+-----------+-----------------------+------+-----+-------------------+-------------------+
| id        | int                   | NO   | PRI | NULL              | auto_increment    |
| code      | char(8)               | NO   | UNI | NULL              |                   |
| amount    | decimal(8,2)          | NO   |     | 1000.00           |                   |
| status    | enum('active','used') | NO   |     | active            |                   |
| issued_at | datetime              | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-----------+-----------------------+------+-----+-------------------+-------------------+
5 rows in set (0.00 sec)

Полную картину даёт SHOW CREATE TABLE:

SHOW CREATE TABLE gift_cards\G
*************************** 1. row ***************************
       Table: gift_cards
Create Table: CREATE TABLE `gift_cards` (
  `id` int NOT NULL AUTO_INCREMENT,
  `code` char(8) NOT NULL,
  `amount` decimal(8,2) NOT NULL DEFAULT '1000.00',
  `status` enum('active','used') NOT NULL DEFAULT 'active',
  `issued_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  CONSTRAINT `chk_gift_amount_max` CHECK ((`amount` <= 50000)),
  CONSTRAINT `gift_cards_chk_1` CHECK ((`amount` > 0))
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

UNIQUE превратился в индекс UNIQUE KEY code: уникальность MySQL проверяет через индекс, иначе пришлось бы перебирать таблицу при каждой вставке.

CHECK работает и при изменении

Проверка срабатывает не только при вставке:

UPDATE gift_cards SET amount = -5 WHERE id = 1;
ERROR 3819 (HY000): Check constraint 'gift_cards_chk_1' is violated.

Решили продавать карты дороже 50 000? Убираем проверку по имени. Вот где имя пригодилось:

ALTER TABLE gift_cards DROP CHECK chk_gift_amount_max;
INSERT INTO gift_cards (code, amount) VALUES ('GIFT0006', 70000);
SELECT code, amount FROM gift_cards;
+----------+----------+
| code     | amount   |
+----------+----------+
| GIFT0001 |  1000.00 |
| GIFT0005 |   500.00 |
| GIFT0006 | 70000.00 |
+----------+----------+
3 rows in set (0.00 sec)

Последняя деталь. CHECK отказывает, только когда условие ложно. Для NULL условие x > 0 не ложно, а неизвестно, и строка проходит:

CREATE TABLE check_null (x INT, CHECK (x > 0));
INSERT INTO check_null VALUES (NULL);
SELECT * FROM check_null;
+------+
| x    |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

Поэтому CHECK почти всегда идёт в паре с NOT NULL, как у amount. Те же ограничения в PostgreSQL разобраны в уроке про ограничения PostgreSQL.

Что запомнить

  • NOT NULL делает поле обязательным, без значения по умолчанию сервер ответит ошибкой 1364.
  • UNIQUE не пускает повторы и сравнивает их по сортировке, поэтому адрес в другом регистре тоже повтор.
  • DEFAULT подставляет значение, когда колонку не назвали в INSERT.
  • CHECK проверяет каждую новую и изменённую строку, а NULL в условии он пропускает.
  • Ошибка вставки всё равно тратит номер AUTO_INCREMENT, отсюда дыры в нумерации.

Ограничения стоят в одном месте для всех программ, которые пишут в базу. Проверка в форме сайта защищает только этот сайт, а NOT NULL, UNIQUE и CHECK сработают и для скрипта импорта, и для администратора с консолью.

Попробуй сам

В песочнице создадим таблицу подарочных карт и проверим каждое правило:

Добавь в таблицу проверку CONSTRAINT chk_gift_amount_min CHECK (amount > 0) и вставь карту на ноль рублей. Вставь карту с явным NULL в status. Вставь карту после ошибки с дублем и посмотри, какой id она получила.

Проверь себя

Задание

Дальше

Мы создавали таблицы с нуля. Но живая таблица меняется: нужна новая колонка, шире строка, другой индекс. В следующем уроке разбираем ALTER TABLE и выносим режиссёров фильмов в отдельную таблицу.

домашка

Домашка