ORA-02266错误的批量生成脚本解决方案
ora-02266: unique/primary keys in table referenced by enabled foreign keys这篇博客是很早之前总结的一篇文章,最近导数时使用truncate清理主表数据又遇到了这个错误,发现还有其它解决方案:
a) 禁用与主表相关的外键约束
b) truncate table
c) 启用那些外键约束。
在实际操作中,发现使用上面的流程操作虽然正确,但是要写很多脚本,有些主表中的字段可能是多个表的外键约束。那么我们必须写多个脚本,那么我们必须使用脚本批量生成。
快速解决问题,具体如下所示:。
--生成禁用约束的脚本,解决ora-02266: unique/primary keys in table referenced by enabled foreign keys
select dc.owner as "parent_table_owner",
dc.table_name as "parent_table_name",
dc.constraint_name as "primary constraint name",
df.constraint_name as "referenced constraint name",
df.owner as "child_table_owner",
df.table_name as "child_table_name" ,
'alter table ' || df.owner || '.' || df.table_name || ' disable constraint ' || df.constraint_name || ';'
from dba_constraints dc,
(select c.owner,
c.constraint_name,
c.r_constraint_name,
c.table_name
from dba_constraints c
where constraint_type = 'r') df
where dc.constraint_name = df.r_constraint_name
and dc.owner =upper('&owner')
and dc.table_name=upper('&table_name');
--生成启用约束的脚本,解决ora-02266: unique/primary keys in table referenced by enabled foreign keys
select dc.owner as "parent_table_owner",
dc.table_name as "parent_table_name",
dc.constraint_name as "primary constraint name",
df.constraint_name as "referenced constraint name",
df.owner as "child_table_owner",
df.table_name as "child_table_name" ,
'alter table ' || df.owner || '.' || df.table_name || ' enable constraint ' || df.constraint_name || ';'
from dba_constraints dc,
(select c.owner,
c.constraint_name,
c.r_constraint_name,
c.table_name
from dba_constraints c
where constraint_type = 'r') df
where dc.constraint_name = df.r_constraint_name
and dc.owner =upper('&owner')
and dc.table_name=upper('&table_name');
原因分析:对于由 foreign key 约束引用的表,不能使用 truncate table,因为truncate不会触发触发器,不会去验证任何约束。所以语法上是不允许的:有外键约束的表只能用delete删除数据,不能用truncate删除数据。
you cannot truncate a table with an enabled foreign key that points to it. truncate does not fire any triggers, does not validate any constraints. it does not care of the child table is empty or not -- in this case the child table might actually not be empty.
上一篇: C语言:时间复杂度为O(1)的两则小程序
推荐阅读
-
自动生成批量执行SQL脚本的批处理实例演示
-
ORA-02266错误的批量生成脚本解决方案
-
分享一款一直在维护的【网络开发运维|通用调试工具】: http请求, websocket,cmd, RSA,DES, 参数签名工具,脚本批量生成工具,google动态口令,端口检测,组件注册,js混淆...
-
解决方案:PowerDesigner 16生成的SqlServer 2000/2005/2008脚本
-
解决方案:PowerDesigner 16生成的SqlServer 2000/2005/2008脚本
-
解决方案:PowerDesigner 16生成的SqlServer 2000/2005/2008脚本执行出现:“对象名
-
sql server自动生成批量执行SQL脚本的批处理
-
解决方案:PowerDesigner 16生成的SqlServer 2000/2005/2008脚本执行出现:“对象名
-
sql server自动生成批量执行SQL脚本的批处理
-
自动生成批量执行SQL脚本的批处理实例演示