运行INSERT INTO xxx(col)SELECT时,我遇到
MySQL 5.6 InnoDb忽略NOT NULL外键的问题….当以其他格式运行insert语句时,约束被正确执行.启用了外键检查,并且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会为3个插入语句中的2个强制执行外键.有任何想法吗？
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请联系我们，一经查实，本站将立刻删除。
如需转载请保留出处：https://51itzy.com/kjqy/40780.html