请教一个奇怪的约束错误

显示全部楼层 · 2007-4-25 04:02:08

在执行
delete fromsalesordermaster

WHERE salesname IN (SELECT salesname

FROM mst_salesmaster

WHERE sys_source = 'KANBAN_SALES') --7500550377_000_001_000000

AND sys_auth_id = 'CA'

AND bu in (SELECT * FROM TABLE (mdm_base_function('0010').bu_list));
系统报约束错误。ORA-02292: integrity constraint (S5119577000_4634) violated - child record found
但是子表中已经确定没有相关数据了。
如果把 bu in (SELECT * FROM TABLE (mdm_base_function('0010').bu_list)) 这个条件改成
bu = (SELECT * FROM TABLE (mdm_base_function('0010').bu_list)) 则不会报错。
其中bu_list是自定义的类型：table of varchar2（40）。
SELECT * FROM TABLE (mdm_base_function('0010').bu_list) 只返回一个值。
请各位大侠帮忙看看是什么原因。

千问 · 2007-4-25 04:02:08

还有什么奇怪的事情啊，难道是bug???
看看两个查询的执行计划区别

千问 · 2007-4-25 04:02:08

看看这个方案

OERR: ORA 2292 integrity constraint .violated - child record found [ID 19525.1]
--------------------------------------------------------------------------------

修改时间 10-JUN-1999 类型 REFERENCE 状态 PUBLISHED

Error:ORA 2292
Text: integrity constraint . violated - child record found
-------------------------------------------------------------------------------
Cause:An attempt was made to delete a row that is referenced by a foreign
key.
Action: It is necessary to DELETE or UPDATE the foreign key before changing
this row.

*** Important: The notes below are for experienced users - See Note:22080.1

Explanation:

ORA 2292 will occur when you try to update or delete a PARENT record

which has CHILD records which would be left 'orphaned' by the update.
Eg:

create table t1 ( pk number , t1text varchar2(10) );

create table t2 ( fk number , t2text varchar2(10) );

alter table t1 add constraint pk1 primary key (pk);

alter table t2 add constraint fk1 foreign key (fk) references t1(pk);

insert into t1 values ( 1, 'master' );

insert into t2 values ( 1, 'child' ):

commit;

update t1 set pk=2 where pk=1;

>> will raise ORA 2292

An attempt to work around this with database triggers may look like

this - (it wont work but dont worry about that yet - read on):

create or replace trigger tr1 after update of PK on T1

for each row

begin

update t2 set fk=:new.pk

where fk=

ld.fk;

end;

This is the logical way forward but this will give you ORA 4091 errors

as this UPDATE needs to look at T1 to check the referential integrity

constraint FK1. It cannot look at T1 as T1 is mutating.

To avoid the ORA 4091 use the techniques in Note:37861.1
Articles / Known Issues:

相关的

--------------------------------------------------------------------------------
产品
--------------------------------------------------------------------------------
Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
关键字
--------------------------------------------------------------------------------
OERRTAG

千问 · 2007-4-25 04:02:08

可以设定一个触发器来处理子表的数据

千问 · 2007-4-25 04:02:08

这是IN的执行计划
DELETE STATEMENT, GOAL = ALL_ROWS
DELETE
Object name=SALESORDERMASTER
HASH JOIN RIGHT SEMI
TABLE ACCESS FULL
Object name=MST_SALESMASTER
HASH JOIN SEMI
TABLE ACCESS FULL Object name=SALESORDERMASTER
COLLECTION ITERATOR PICKLER FETCH
这是=的执行计划
DELETE STATEMENT, GOAL = ALL_ROWS
DELETE
Object name=SALESORDERMASTER
HASH JOIN RIGHT SEMI
TABLE ACCESS FULL
Object name=MST_SALESMASTER
TABLE ACCESS FULL
Object name=SALESORDERMASTER
COLLECTION ITERATOR PICKLER FETCH
两者好像没有什么区别。
附上bu_list的取值方法，是不是多次的类型转换会带来什么问题。
SELECT CAST

(MULTISET (SELECT DISTINCT bu

FROM ud_site_schedule

WHERE ID = ‘0010’

AND plant NOT IN (

SELECT siteid

FROM mid_ui_block_so

WHERE blocked = 'Y')

) AS list_type

)
INTO bu_list
FROM DUAL;
CREATE OR REPLACE TYPE list_typeIS TABLE OFvarchar(40)；

千问 · 2007-4-25 04:02:08

子表中的相关数据，已经在先前的业务处理中，已经完全删除了。而且经检查，肯定是没有了。
理论上说，应该是不能报这个约束错误啊。
而且为什么换了写法它就能正常执行过去呢？
所以我感觉在执行
SELECT * FROM TABLE (mdm_base_function('0010').bu_list)这个语句的时候，是不是有什么问题。但是我现在还知道。