在测试环境执行某医院的升级脚本时,遇到下面的错误(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的错误提示走不通的时候,思维要打开,根据它的原理去顺藤摸瓜,就容易查到真正的原因,并找到解决办法。
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/