mysql - sql - FK 的多行插入

Question

我有下表:

| order_details | CREATE TABLE `order_details` (
  `order_id` int(11) NOT NULL,
  `client_id` int(11) NOT NULL,
  `product_id` varchar(10) NOT NULL,
  `serial` int(11) NOT NULL,
  `detail_id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`detail_id`),
  KEY `order_id` (`order_id`),
  KEY `client_id` (`client_id`),
  CONSTRAINT `order_details_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`),
  CONSTRAINT `order_details_ibfk_2` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`)
) ENGINE=InnoDB AUTO_INCREMENT=57 DEFAULT CHARSET=utf8 |

我尝试在 client_id 列的多行中插入值:

insert into order_details
    (`client_id`)
    values
    (1),
    (1),
    (1),
    (1),
    (2),
    (3),
    (14),
    (4),
    (5),
    (5),
    (5),
    (5),
    (7),
    ...
    (12),
    (13);

当我尝试在关闭严格模式的情况下执行插入时，收到外键约束失败(错误:1452)。但是，它似乎试图插入 order_id 列而不是 client_id (请参阅 err msg)。可能是什么原因导致此错误以及如何将插入重定向到 client_id 列？

错误消息:

Cannot add or update a child row: a foreign key constraint fails (db.order_details, CONSTRAINT order_details_ibfk_1 FOREIGN KEY (order_id) REFERENCES orders (order_id))

Answer 1

您需要在插入中列出所有非空列:

insert into order_details
    (`client_id`, [other columns here])
values
(1, [other values here]),