AiDOPWarehouse/tools/sql/fix_mes_tables_primary_keys.sql

-- 修复 mes_moentry 和 mes_morder 表的主键问题
-- 执行此脚本前，请先执行 check_mes_tables_primary_keys.sql 检查当前主键情况

USE aidopdev;

-- 如果 mes_moentry 表有多个主键约束，删除后重建
-- 注意：如果表中已有数据，请谨慎操作并备份数据

-- 1. 删除 mes_moentry 表的现有主键（如果存在多个）
SET @drop_pk_moentry = (SELECT IF(
    COUNT(*) > 0,
    'ALTER TABLE mes_moentry DROP PRIMARY KEY',
    'SELECT ''mes_moentry PRIMARY KEY not found'' AS info'
) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'aidopdev' 
  AND TABLE_NAME = 'mes_moentry'
  AND CONSTRAINT_TYPE = 'PRIMARY KEY');

PREPARE stmt FROM @drop_pk_moentry;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 2. 重新添加 mes_moentry 表的主键（只以 Id 作为主键）
SET @add_pk_moentry = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE mes_moentry ADD PRIMARY KEY (Id)',
    'SELECT ''mes_moentry PRIMARY KEY already exists'' AS info'
) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'aidopdev' 
  AND TABLE_NAME = 'mes_moentry'
  AND CONSTRAINT_TYPE = 'PRIMARY KEY');

PREPARE stmt FROM @add_pk_moentry;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 3. 删除 mes_morder 表的现有主键（如果存在多个）
SET @drop_pk_morder = (SELECT IF(
    COUNT(*) > 0,
    'ALTER TABLE mes_morder DROP PRIMARY KEY',
    'SELECT ''mes_morder PRIMARY KEY not found'' AS info'
) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'aidopdev' 
  AND TABLE_NAME = 'mes_morder'
  AND CONSTRAINT_TYPE = 'PRIMARY KEY');

PREPARE stmt FROM @drop_pk_morder;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 4. 重新添加 mes_morder 表的主键（只以 Id 作为主键）
SET @add_pk_morder = (SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE mes_morder ADD PRIMARY KEY (Id)',
    'SELECT ''mes_morder PRIMARY KEY already exists'' AS info'
) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'aidopdev' 
  AND TABLE_NAME = 'mes_morder'
  AND CONSTRAINT_TYPE = 'PRIMARY KEY');

PREPARE stmt FROM @add_pk_morder;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT 'Primary keys have been fixed for mes_moentry and mes_morder' AS result;