Первичные ключи с nullable столбцами

Рассмотрим следующую структуру таблицы:

CREATE TABLE t1(
  c1 INT NOT NULL AUTO_INCREMENT, 
  c2 INT NULL DEFAULT NULL, 
  PRIMARY KEY(c1,c2)
);

Столбец c2 является частью первичного ключа, и поэтому он не может быть NULL.

До MariaDB 10.1.7, MariaDB (а также версии MySQL до MySQL 5.7) молчаливо преобразовывали его в столбец NOT NULL со значением по умолчанию 0.

Начиная с MariaDB 10.1.7, столбец преобразуется в NOT NULL, но без значения по умолчанию. Если затем мы попытаемся вставить запись без явного задания c2, будет выброшено предупреждение (или, в строгом режиме, ошибка), например:

INSERT INTO t1() VALUES();
Query OK, 1 row affected, 1 warning (0.00 sec)
Warning (Code 1364): Field 'c2' doesn't have a default value

SELECT * FROM t1;
+----+----+
| c1 | c2 |
+----+----+
|  1 |  0 |
+----+----+

MySQL, начиная с версии 5.7, прервет такое создание таблицы с ошибкой.

Поведение MariaDB 10.1.7 соответствует стандарту SQL 2003.

SQL-2003, Часть II, «Основы» гласит:

11.7 <определение уникального ограничения>
Правила синтаксиса

…

5) Если <уникальное указание> задает PRIMARY KEY, то для каждого <имени столбца> в явном или неявном <списке столбцов уникальности>, для которого не указано NOT NULL, NOT NULL неявно присутствует в <определении столбца>.

По сути, это означает, что все столбцы PRIMARY KEY автоматически преобразуются в NOT NULL. Кроме того:

11.5 <оператор по умолчанию>
Общие правила

…

3) Когда сайт S устанавливается в свое значение по умолчанию,

…

b) Если описание данных для сайта включает <вариант по умолчанию>, то S устанавливается в указанное значение <варианта по умолчанию>.

…

e) В противном случае S устанавливается в значение NULL.

В стандарте нет понятия «нет значения по умолчанию». Вместо этого столбец всегда имеет неявное значение по умолчанию NULL. При вставке, однако, он может нарушить ограничение NOT NULL. MariaDB и MySQL вместо этого помечают такой столбец как «не имеющий значения по умолчанию». Конечный результат остается неизменным — значение должно быть указано явно, иначе INSERT завершится ошибкой.

MariaDB начиная с 10.1.7 ведет себя в соответствии со стандартом — будучи частью PRIMARY KEY, nullable столбец получает автоматическое ограничение NOT NULL, при вставке необходимо указать значение для такого столбца. MariaDB до 10.1.7 автоматически присваивал значение по умолчанию 0 — это поведение нестандартно. Выдача ошибки во время CREATE TABLE также нестандартна.

См. также

• MDEV-12248 описывает частный случай, который может привести к проблемам репликации при репликации с сервера-мастера на сервер-ведомого до этого изменения.