一例外键约束创建失败的分析及问题解决

在测试环境执行某医院的升级脚本时，遇到下面的错误（Oracle的这个错误提示相当有欺骗性，后面细说）

ORA-02270: 此列列表的唯一关键字或主键不匹配
Alter Table 病人护理要素内容 Add Constraint 病人护理要素内容_FK_文件ID Foreign Key (文件ID) References 病人护理文件(ID) On Delete Cascade


把该SQL拷贝出来在PLSQL中运行仍然报相同的错误。
根据提示，推测可能是子表"病人护理要素内容"中存在的文件ID在父表中不存在对应主键字段的记录，查询该表却发现没有任何数据。
空表不可能存在与主表的主键不匹配的记录。
这就奇怪了，然后，检查user_cons_columns，检查主表的主键字段是否正确（是不是像创建外键SQL中的那样叫ID），没有发现问题。
再检查user_tab_cols，检查主、从表的两个字段数据类型是否一致，结果都是Number(18)，仍然没有问题。


最后，检查user_indexes中主表的主键索引"病人护理文件_PK"，发现没有这个索引，问题就找出来了。
原来主表的主键约束对应索引丢失了，这种情况一般是导入数据等情况导致的。
可以看到，Oracle给出的ORA-002270，很容易误导我们，以为是数据违反了唯一约束或参照完整性等情况。


后来，还发现一种情况，主键虽然有同名的索引，但是却没有跟主键约束关联起来，用下面的SQL查询，查出的索引名列为空：
select index_name from user_constraints where constraint_name = '病人护理文件_PK';
这种情况通常也是因为导入数据产生的，一般发生在从8i等低版本升级到高版本后再导出导入的环境。




问题找到了，解决办法有几种：
1.创建与主键同名的索引，然后将索引与约束关联，SQL如下
SQL> create index 病人护理文件_PK on 病人护理文件(ID) Tablespace zl9Indexcis;
Index created


SQL> Alter Table 病人护理文件 modify Constraint 病人护理文件_PK using index 病人护理文件_PK;
Table altered


这里的using index 就是将后面指定的索引关联到主键约束的意思，然后，创建外键就顺利通过了。
SQL> Alter Table 病人护理要素内容 Add Constraint 病人护理要素内容_FK_文件ID Foreign Key (文件ID) References 病人护理文件(ID) On Delete Cascade;
Table altered


2.禁用主表的主键约束再恢复，恢复时会自动创建主键同名的索引。
  或者删除主表的主键约束，再重建。
  这两种方法都需要先禁用引用该主键的其他外键，才能删除或禁用主表的主键，查询如下：
SQL> select constraint_name  from user_constraints where r_constraint_name = '病人护理文件_PK';
CONSTRAINT_NAME
------------------------------
病人护理打印_FK
病人护理活动项目_FK_文件ID
病人护理数据_FK
产程要素内容_FK_文件ID


可以看到，引用该主键的外键有4个。
这种处理方式比较麻烦，所以，推荐采用第一种方式，只需要两句SQL。


小结：
有时遇到按Oracle的错误提示走不通的时候，思维要打开，根据它的原理去顺藤摸瓜，就容易查到真正的原因，并找到解决办法。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/117319/viewspace-1590379/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/117319/viewspace-1590379/