SQLError-创建外键

Question

尝试添加外键时出错

SQLError

在此之前，执行了以下操作，安装了索引

主桌

从属

根据https://folkprog.net/sozdanie-svyazey-phpmyadmin/.

错误文本：

更改表monoblocks在删除限制上添加外键(image_id(引用monoblock_images(id(在更新限制上添加；

#1452-无法添加或更新子行：外键约束失败(myishop.#sql-2150_93，constraint#sql-2150_93_ibfk_21foreign key(image_id(REFERENCESmonoblock_images(id((

我找到

CREATE TABLE `monoblocks` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`goods_id` int(11) NOT NULL,
`screendiagonal_id` int(11) NOT NULL,
`cpu_id` int(11) NOT NULL,
`ramsize_id` int(11) NOT NULL,
`os_id` int(11) NOT NULL,
`hdd_id` int(11) NOT NULL,
`screenresolution_id` int(11) NOT NULL,
`matrixtype_id` int(11) NOT NULL,
`countcpucores` int(11) NOT NULL,
`videocards_id` int(11) NOT NULL,
`videocardtypes_id` int(11) NOT NULL,
`frequency_id` int(11) NOT NULL,
`ramtype_id` int(11) NOT NULL,
`oddtype_id` int(11) NOT NULL,
`bodycolor_id` int(11) NOT NULL,
`weight` int(11) NOT NULL,
`wifisupport_id` int(11) NOT NULL,
`bluetoothsupport_id` int(11) NOT NULL,
`wifistandard_id` int(11) NOT NULL,
`bluetoothstandard_id` int(11) NOT NULL,
`guarantee_id` int(11) NOT NULL,
`connectorsnumberusb2.0` int(11) NOT NULL,
`connectorsnumberusb3.0` int(11) NOT NULL,
`connectorsnumberhdmi` int(11) NOT NULL,
`microphoneconnecors` int(11) NOT NULL,
`headphoneconnectors` int(11) NOT NULL,
`builtinmicrophone` tinyint(1) NOT NULL,
`builtinspeakers` tinyint(1) NOT NULL,
`touchscreen` tinyint(1) NOT NULL,
`webcam` tinyint(1) NOT NULL,
`keyboard_included` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`mouse_included` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`ssd_volume` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`dimensions` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`image_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `goods_id` (`goods_id`),
KEY `screendiagonal_id` (`screendiagonal_id`),
KEY `cpu_id` (`cpu_id`),
KEY `ramsize_id` (`ramsize_id`),
KEY `os_id` (`os_id`),
KEY `hdd` (`hdd_id`),
KEY `screenresolution_id` (`screenresolution_id`),
KEY `matrixtype_id` (`matrixtype_id`),
KEY `countcpucores` (`countcpucores`),
KEY `videocards_id` (`videocards_id`),
KEY `videocardtypes_id` (`videocardtypes_id`),
KEY `frequency_id` (`frequency_id`),
KEY `ramtype_id` (`ramtype_id`),
KEY `oddtype_id` (`oddtype_id`),
KEY `bodycolor_id` (`bodycolor_id`),
KEY `wifisupport_id` (`wifisupport_id`),
KEY `bluetoothsupport_id` (`bluetoothsupport_id`),
KEY `wifistandard_id` (`wifistandard_id`),
KEY `bluetoothstandard_id` (`bluetoothstandard_id`),
KEY `guarantee_id` (`guarantee_id`),
KEY `image_id` (`image_id`),
CONSTRAINT `monoblocks_ibfk_1` FOREIGN KEY (`goods_id`) REFERENCES `goods` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `monoblocks_ibfk_10` FOREIGN KEY (`videocards_id`) REFERENCES `videocards` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `monoblocks_ibfk_11` FOREIGN KEY (`videocardtypes_id`) REFERENCES `videocardtypes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `monoblocks_ibfk_12` FOREIGN KEY (`frequency_id`) REFERENCES `cpufrequency` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `monoblocks_ibfk_13` FOREIGN KEY (`ramtype_id`) REFERENCES `rammemory` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `monoblocks_ibfk_14` FOREIGN KEY (`oddtype_id`) REFERENCES `oddtype` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `monoblocks_ibfk_15` FOREIGN KEY (`bodycolor_id`) REFERENCES `mainbodycolor` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `monoblocks_ibfk_16` FOREIGN KEY (`wifisupport_id`) REFERENCES `wifisupport` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `monoblocks_ibfk_17` FOREIGN KEY (`bluetoothsupport_id`) REFERENCES `bluetoothsupport` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `monoblocks_ibfk_18` FOREIGN KEY (`wifistandard_id`) REFERENCES `wifistandard` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `monoblocks_ibfk_19` FOREIGN KEY (`bluetoothstandard_id`) REFERENCES `bluetoothstandard` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `monoblocks_ibfk_2` FOREIGN KEY (`screendiagonal_id`) REFERENCES `screendiagonal` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `monoblocks_ibfk_20` FOREIGN KEY (`guarantee_id`) REFERENCES `guarantee` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `monoblocks_ibfk_3` FOREIGN KEY (`cpu_id`) REFERENCES `cpus` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `monoblocks_ibfk_4` FOREIGN KEY (`ramsize_id`) REFERENCES `ramsize` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `monoblocks_ibfk_5` FOREIGN KEY (`os_id`) REFERENCES `os` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `monoblocks_ibfk_6` FOREIGN KEY (`hdd_id`) REFERENCES `disksandrives` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `monoblocks_ibfk_7` FOREIGN KEY (`screenresolution_id`) REFERENCES `screenresolution` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `monoblocks_ibfk_8` FOREIGN KEY (`matrixtype_id`) REFERENCES `matrixtype` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `monoblocks_ibfk_9` FOREIGN KEY (`countcpucores`) REFERENCES `numbercpucores` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `monoblock_images` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`path_to_image` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

(可能有助于回答问题(

在SELECT mb.image_id FROM monoblocks AS mb WHERE NOT EXISTS (SELECT 1 FROM monoblock_images AS i WHERE i.id = mb.image_id) AND mb.image_id IS NOT NULL的回答中，我给出了

单块查询

Answer 1

image_id在表monoblocks中可以为NULL，但在其他表中不能为NULL。