Laravel迁移月度信贷记录到历史表报MySQL1452外键约束错误

已核对同类型1452外键报错的所有常规校验项，配置、数据均符合要求，问题仍未解决。
现有两张结构完全一致的信贷业务表：

• credit_records：存储当月生效的信贷记录
• credit_records_historic：存储归档的历史信贷记录

执行月度数据归档（将当月表数据迁移写入历史表）操作时，抛出如下外键约束错误：

Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails credit_records_historic, CONSTRAINT credit_record_historic_id_manager_foreign FOREIGN KEY (id_manager) REFERENCES managers (id)

表结构定义 #

当月信贷表credit_records建表语句 #

CREATE TABLE IF NOT EXISTS `credit_records` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `id_client` int(10) unsigned NOT NULL,
    `id_user` int(10) unsigned NOT NULL,
    `value` smallint(6) NOT NULL,
    `id_type` int(10) unsigned DEFAULT NULL,
    `observations` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `id_terminal` int(10) unsigned DEFAULT NULL,
    `id_manager` int(10) unsigned DEFAULT NULL,
    `id_institution` int(10) unsigned DEFAULT NULL,
    `sended` tinyint(1) NOT NULL DEFAULT 0,
    `received` tinyint(1) NOT NULL DEFAULT 0,
    `date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
    PRIMARY KEY (`id`),
    KEY `credit_record_id_client_foreign` (`id_client`),
    KEY `credit_record_id_user_foreign` (`id_user`),
    KEY `credit_record_id_type_foreign` (`id_type`),
    KEY `credit_record_id_terminal_foreign` (`id_terminal`),
    KEY `credit_record_id_manager_foreign` (`id_manager`),
    KEY `credit_records_id_institution_foreign` (`id_institution`),
    CONSTRAINT `credit_record_id_client_foreign` FOREIGN KEY (`id_client`) REFERENCES `clients` (`id`),
    CONSTRAINT `credit_record_id_manager_foreign` FOREIGN KEY (`id_manager`) REFERENCES `managers` (`id`),
    CONSTRAINT `credit_record_id_terminal_foreign` FOREIGN KEY (`id_terminal`) REFERENCES `terminals` (`id`),
    CONSTRAINT `credit_record_id_type_foreign` FOREIGN KEY (`id_type`) REFERENCES `types` (`id`),
    CONSTRAINT `credit_record_id_user_foreign` FOREIGN KEY (`id_user`) REFERENCES `users` (`id`),
    CONSTRAINT `credit_records_id_institution_foreign` FOREIGN KEY (`id_institution`) REFERENCES `institutions` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2124 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

历史归档表credit_records_historic建表语句 #

CREATE TABLE IF NOT EXISTS `credit_records_historic` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `id_client` int(10) unsigned NOT NULL,
    `id_user` int(10) unsigned NOT NULL,
    `value` smallint(6) NOT NULL,
    `id_type` int(10) unsigned DEFAULT NULL,
    `observations` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `id_terminal` int(10) unsigned DEFAULT NULL,
    `id_manager` int(10) unsigned DEFAULT NULL,
    `id_institution` int(10) unsigned DEFAULT NULL,
    `sended` tinyint(1) NOT NULL DEFAULT 0,
    `received` tinyint(1) NOT NULL DEFAULT 0,
    `date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
    PRIMARY KEY (`id`),
    KEY `credit_record_historic_id_client_foreign` (`id_client`),
    KEY `credit_record_historic_id_user_foreign` (`id_user`),
    KEY `credit_record_historic_id_type_foreign` (`id_type`),
    KEY `credit_record_historic_id_terminal_foreign` (`id_terminal`),
    KEY `credit_record_historic_id_manager_foreign` (`id_manager`),
    KEY `credit_records_historic_id_institution_foreign` (`id_institution`),
    CONSTRAINT `credit_record_historic_id_client_foreign` FOREIGN KEY (`id_client`) REFERENCES `clients` (`id`),
    CONSTRAINT `credit_record_historic_id_manager_foreign` FOREIGN KEY (`id_manager`) REFERENCES `managers` (`id`),
    CONSTRAINT `credit_record_historic_id_terminal_foreign` FOREIGN KEY (`id_terminal`) REFERENCES `terminals` (`id`),
    CONSTRAINT `credit_record_historic_id_type_foreign` FOREIGN KEY (`id_type`) REFERENCES `types` (`id`),
    CONSTRAINT `credit_record_historic_id_user_foreign` FOREIGN KEY (`id_user`) REFERENCES `users` (`id`),
    CONSTRAINT `credit_records_historic_id_institution_foreign` FOREIGN KEY (`id_institution`) REFERENCES `institutions` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2013 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

管理员表managers建表语句 #

CREATE TABLE IF NOT EXISTS `managers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `surnames` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `username` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `slug` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `id_lang` int(10) unsigned NOT NULL,
  `id_role` int(10) unsigned NOT NULL,
  `state` tinyint(3) unsigned NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `managers_username_unique` (`username`),
  UNIQUE KEY `managers_email_unique` (`email`),
  KEY `managers_id_lang_foreign` (`id_lang`),
  KEY `managers_id_role_foreign` (`id_role`),
  CONSTRAINT `managers_id_lang_foreign` FOREIGN KEY (`id_lang`) REFERENCES `langs` (`id`),
  CONSTRAINT `managers_id_role_foreign` FOREIGN KEY (`id_role`) REFERENCES `roles` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

数据核验结果 #

• 报错关联的ID为5的管理员记录真实存在于managers表，插入语句如下：

INSERT INTO `managers` 
            (`id`, `name`, `surnames`, `username`, `slug`, `email`, 
            `password`, 
            `id_lang`, `id_role`, `state`, 
            `created_at`, `updated_at`, `deleted`, `deleted_at`) 
VALUES (5, 'q', 'q', 'q@q.q', 'q', 'q@q.q', 
        '$2y$10$omstJzdZPxt4NKNEBSLXwuOGFW7RP286NM7pFLPifFSpSuv.uj0Pu', 
        1, 2, 1, 
        '2022-01-20 13:12:49', '2022-01-20 13:12:49', 0, NULL);

• 两张信贷表结构完全一致，两张表中均存在关联ID=5管理员的业务数据
• 完整报错日志如下：

local.ERROR: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (credit_records_historic, CONSTRAINT credit_record_historic_id_manager_foreign FOREIGN KEY (id_manager) REFERENCES managers (id)) (SQL: insert into credit_records_historic (id_client, id_user, value, id_type, observations, id_terminal, id_manager, id_institution, sended, received, date) values (1, 4, 100, 11, Nemo et dolores quis. Omnis doloremque dignissimos blanditiis officiis tenetur. Ab itaque voluptatibus est quia., 1, 5, 2, 0, 0, 2022-02-17 17:15:30))

• 触发报错的插入语句如下：

insert into credit_records_historic 
        (id_client, id_user, value, id_type, 
        observations, 
        id_terminal, id_manager, id_institution, sended, received, date) 
values (1, 4, 100, 11, 
        Nemo et dolores quis. Omnis doloremque dignissimos blanditiis officiis tenetur. Ab itaque voluptatibus est quia., 
        1, 5, 2, 0, 0, 2022-02-17 17:15:30))

迁移实现逻辑 #

项目基于Laravel框架开发，数据迁移代码如下：

CreditRecord::query()
        ->where('id','>', '0')
        ->each(function ($old_record) {
            $new_record = $old_record->replicate();
            $new_record->setTable('credit_records_historic');
            $new_record->save();
            $old_record->delete();
        });

目前已确认关联管理员数据存在、两张业务表结构完全一致，迁移操作仍持续触发id_manager字段的外键约束报错，需要对应的问题排查思路与解决方案。

内容的提问来源于stack exchange，提问作者Agustín Tamayo Quiñones