问题背景

Oracle租户模式

ob版本：OBcloud 4.2.5.2

在采用oms迁移ob-》ob表结构过程中，出现失败现象， 于是通过将源库ob表结构DDL语句，放在ODC新的ob schema中执行时，报错如下：

ErrorCode = 2261, SQLState = HY000, Details = ORA-02261: such unique or primary key already exists in the table

问题根源：

• OceanBase允许存在同一字段既作为主键（Primary Key）又作为唯一键（Unique Key）

• 当将OB的DDL语句直接在新的OB或Oracle环境中执行时，会检测到重复的约束定义，导致表结构迁移失败。

验证结论：
通过OceanBase技术支持确认，这是OB在兼容Oracle模式下的一个已知问题（BUG）。其约束逻辑与原生Oracle不一致，导致迁移时约束冲突。

问题复现与分析

1. Oracle原生行为

在Oracle中，如果尝试为同一个字段组合同时创建主键和唯一键，会直接报错 ORA-02261：

-- 示例：在Oracle中直接报错
ALTER TABLE t1 ADD CONSTRAINT uk_t1 UNIQUE (id); 

ALTER TABLE t1 ADD CONSTRAINT pk_t1 PRIMARY KEY (id);

ORA-02261: 表中已存在这样的唯一关键字或主键

2. OceanBase行为

可通过以下方式复现

可成功创建guid字段的唯一键和主键。

OceanBase在兼容Oracle模式下允许这种设计，但在以下场景会引发问题：

• 迁移表结构：将OB的DDL迁移到其他OB scheme或者Oracle时，因约束重复而失败。

• 数据一致性风险：冗余的唯一约束可能影响性能，且无实际意义。

解决方案

通过以下步骤解决问题：

1. 识别冗余唯一键：找出所有主键与唯一键字段完全相同的表。

2. 批量删除冗余唯一键：生成并执行删除语句，确保迁移前表结构符合Oracle约束规则。

步骤1：查询存在冲突的表

通过数据字典视图提取主键和唯一键的列组合，筛选出完全相同的记录。

WITH pk_constraints AS (
    SELECT 
        c.owner,
        c.table_name,
        LISTAGG(cc.column_name, ',') WITHIN GROUP (ORDER BY cc.position) AS pk_columns
    FROM 
        all_constraints c
        JOIN all_cons_columns cc ON c.owner = cc.owner AND c.constraint_name = cc.constraint_name
    WHERE 
        c.constraint_type = 'P'
    GROUP BY 
        c.owner, c.table_name
),
uk_constraints AS (
    SELECT 
        c.owner,
        c.table_name,
        LISTAGG(cc.column_name, ',') WITHIN GROUP (ORDER BY cc.position) AS uk_columns
    FROM 
        all_constraints c
        JOIN all_cons_columns cc ON c.owner = cc.owner AND c.constraint_name = cc.constraint_name
    WHERE 
        c.constraint_type = 'U'
    GROUP BY 
        c.owner, c.table_name, c.constraint_name
)
SELECT 
    pk.owner,
    pk.table_name,
    pk.pk_columns AS primary_key_columns,
    uk.uk_columns AS unique_key_columns
FROM 
    pk_constraints pk
JOIN 
    uk_constraints uk ON pk.owner = uk.owner AND pk.table_name = uk.table_name
WHERE 
    pk.pk_columns = uk.uk_columns;

输出示例：

步骤2：生成批量删除语句

SELECT 
    'ALTER TABLE ' || uk.owner || '.' || uk.table_name || 
    ' DROP CONSTRAINT ' || uk.constraint_name || ';' AS drop_sql
FROM (
    WITH pk_constraints AS (
        SELECT 
            c.owner,
            c.table_name,
            LISTAGG(cc.column_name, ',') WITHIN GROUP (ORDER BY cc.position) AS pk_columns
        FROM 
            all_constraints c
            JOIN all_cons_columns cc ON c.owner = cc.owner AND c.constraint_name = cc.constraint_name
        WHERE 
            c.constraint_type = 'P'
        GROUP BY 
            c.owner, c.table_name
    ),
    uk_constraints AS (
        SELECT 
            c.owner,
            c.table_name,
            c.constraint_name,
            LISTAGG(cc.column_name, ',') WITHIN GROUP (ORDER BY cc.position) AS uk_columns
        FROM 
            all_constraints c
            JOIN all_cons_columns cc ON c.owner = cc.owner AND c.constraint_name = cc.constraint_name
        WHERE 
            c.constraint_type = 'U'
        GROUP BY 
            c.owner, c.table_name, c.constraint_name
    )
    SELECT 
        uk.owner,
        uk.table_name,
        uk.constraint_name,
        uk.uk_columns
    FROM 
        pk_constraints pk
        JOIN uk_constraints uk ON pk.owner = uk.owner 
            AND pk.table_name = uk.table_name 
            AND pk.pk_columns = uk.uk_columns
) uk;

输出示例：

ALTER TABLE HR.EMPLOYEES DROP CONSTRAINT UK_EMP_ID;
ALTER TABLE SALES.ORDERS DROP CONSTRAINT UK_ORDERS_PK;

总结

通过此方法，解决了OceanBase因约束兼容性问题导致的表结构迁移失败。

核心教训：

• 不同数据库对约束的设计规则可能存在差异，跨数据库迁移时需严格校验约束逻辑。

• 定期清理冗余约束是提升数据库健壮性的有效手段。

最后，建议在表结构设计阶段遵循以下原则：

1. 主键优先：避免为已定义主键的字段重复创建唯一键。

2. 约束精简：仅保留必要的约束，减少迁移兼容性风险。