-- =============================================
|
-- 调岗申请：允许「原部门」「原岗位」为空
|
--
|
-- 问题：hrm_transfer_application.current_dept_id / current_post_id 为 NOT NULL 且无默认值，
|
-- 而客户环境 133 个员工全部没有配置岗位（hrm_employee.post_id 为 NULL），
|
-- 前端提交调岗申请时这两个字段为空 → insert 报
|
-- "Field 'current_post_id' doesn't have a default value" → 调岗申请一直提交失败。
|
--
|
-- 处理：把这两列改为允许 NULL。原部门/原岗位属于「申请时的快照」，员工档案未配置时为空是合理的，
|
-- 不应因此阻断申请。目标部门/目标岗位仍保持 NOT NULL（前端必填，属于业务必需）。
|
--
|
-- 脚本可重复执行（幂等）
|
-- =============================================
|
SET NAMES utf8mb4;
|
|
SET @sch := DATABASE();
|
|
-- 1. 原岗位允许为空
|
SET @nullable := (SELECT IS_NULLABLE FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @sch AND TABLE_NAME = 'hrm_transfer_application' AND COLUMN_NAME = 'current_post_id');
|
SET @sql := IF(@nullable = 'NO',
|
'ALTER TABLE `hrm_transfer_application` MODIFY COLUMN `current_post_id` bigint NULL DEFAULT NULL COMMENT ''原岗位ID（员工未配置岗位时为空）''',
|
'SELECT ''current_post_id 已允许为空'' AS msg');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 2. 原部门允许为空
|
SET @nullable := (SELECT IS_NULLABLE FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @sch AND TABLE_NAME = 'hrm_transfer_application' AND COLUMN_NAME = 'current_dept_id');
|
SET @sql := IF(@nullable = 'NO',
|
'ALTER TABLE `hrm_transfer_application` MODIFY COLUMN `current_dept_id` bigint NULL DEFAULT NULL COMMENT ''原部门ID（员工未配置部门时为空）''',
|
'SELECT ''current_dept_id 已允许为空'' AS msg');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 3. 核对
|
SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @sch AND TABLE_NAME = 'hrm_transfer_application'
|
AND COLUMN_NAME IN ('current_dept_id','current_post_id','target_dept_id','target_post_id')
|
ORDER BY ORDINAL_POSITION;
|
|
SELECT '调岗申请 原部门/原岗位 允许为空 初始化完成' AS result;
|
