В MySQL вставка выбора не требует ограничения NOT NULL

Question

У меня проблема с MySQL 5.6 InnoDb, игнорирующим внешний ключ NOT NULL при запуске INSERT INTO xxx (col) SELECT ..., Ограничение применяется правильно при выполнении операторов вставки в других форматах. Проверка внешнего ключа включена, и sql_mode = STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION

Вот пример:

CREATE TABLE Test_Parent
(
    id BIGINT(18) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    dummy VARCHAR(255)
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci
    COMMENT 'Test parent table';

CREATE TABLE Test_Child
(
    id BIGINT(18) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    fid BIGINT UNSIGNED NOT NULL,
    FOREIGN KEY Fk_Test_Parent_01(fid) REFERENCES Test_Parent(id)
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci
    COMMENT 'Test child table';

INSERT INTO Test_Parent(dummy)
VALUES ('test');

## Here's where the FK constraint should be enforced but isn't ##
INSERT INTO Test_Child(fid)
SELECT id
    FROM Test_Parent
WHERE dummy = 'missing value';

1 row affected in 5ms

## Running an insert with a different format, the constraint is enforced ##
INSERT INTO Test_Child(fid)
VALUES (null);
Column 'fid' cannot be null

## Running this format, the foreign key is also enforced ##
INSERT INTO Test_Child(id, fid)
VALUES (123, (SELECT id FROM Test_Parent WHERE dummy = 'missing value'));
Column 'fid' cannot be null

Я не понимаю, почему MySQL будет применять внешний ключ для 2 из 3 операторов вставки. Есть идеи?

Answer 1

Вводит в заблуждение 1 row affected in 5ms сообщение от вашего клиента может быть источником путаницы здесь. В ветке комментариев, которую вы упомянули, IntelliJ сообщал об этом сообщении, но я запустил ваши четко определенные тестовые таблицы в MySQL 5.6.35 и 5.7.16-ubuntu, и в обеих версиях рассматриваемый оператор сообщил о 0 затронутых строках:

mysql > INSERT INTO Test_Child(fid)
    -> SELECT id
    ->     FROM Test_Parent
    -> WHERE dummy = 'missing value';
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Итак, если смотреть мимо вводящего в заблуждение сообщения о затронутых строках, то, что на самом деле происходит, - SELECT часть вашего INSERT...SELECT оператор не соответствует ни одной строке, и поэтому MySQL не пытается вставить какую-либо строку. Поэтому не было нарушения ограничения внешнего ключа.

Формат INSERT INTO...SELECT немного отличается от вашего более позднего примера:

INSERT INTO Test_Child(id, fid)
VALUES (123, (SELECT id FROM Test_Parent WHERE dummy = 'missing value'));

... потому что в этом случае числовой литерал 123 принудительно вставляет один ряд, и он соединяется с null значение, возвращаемое из подвыбора. Чтобы null попытка вставить и вызывает нарушение ограничения.

Если вы заставляете INSERT...SELECT чтобы вернуть строку с нулевым значением, вы можете сделать ошибку из-за нарушения ограничения:

INSERT INTO Test_Child (fid)
-- Return a literal NULL row...
SELECT NULL as id FROM Test_Parent
-- Column fid cannot be null