• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1452
  • Last Modified:

Oracle 10 How do I find the child part of a constraint?

Oracle 10
When I try to do an update to a record I get a constraint error. I interpret the error to mean that Im trying to change a parent record that has an associated child.

Is my interpretation correct? And if it is how do I find the child table and column(s)?

Thanks
Ron


UPDATE RT_TEST.SERVICE_LINE SET FK_CLAIM_ID = '19872327500137' WHERE FK_CLAIM_ID
= '20072327500137';
BEGIN UPDATE_STATIC_DATA (500); END;
 
*
ERROR at line 1:
ORA-02292: integrity constraint (RT_TEST.CCL0CF00) violated - child record
found
ORA-06512: at "CA50633.UPDATE_STATIC_DATA", line 172
ORA-06512: at line 1
 
 
SQL>
SQL> SELECT * FROM ALL_CONS_COLUMNS
  2  WHERE TABLE_NAME = 'MBR_PROV_NTWK_ASSN'
  3   AND CONSTRAINT_NAME = 'CCL0CF00'
  4  AND OWNER = 'RT_TEST2';
 
no rows selected
 
SQL>
SQL> select * from all_constraints
  2  WHERE CONSTRAINT_NAME = 'CCL0CF00'
  3  AND OWNER = 'RT_TEST2';
 
RT_TEST2                       CCL0CF00                       R
SVC_LIN_EX_CD_ASSN
 
RT_TEST2                       XCL01200                       CASCADE   DISABLED
NOT DEFERRABLE IMMEDIATE NOT VALIDATED USER NAME               11-OCT-07
 
 
 
 
SQL> select * from all_constraints
  2  WHERE CONSTRAINT_NAME = 'XCL01200'
  3  AND OWNER = 'RT_TEST2';
 
RT_TEST2                       XCL01200                       P
SERVICE_LINE
 
                                                                        DISABLED
NOT DEFERRABLE IMMEDIATE NOT VALIDATED USER NAME               12-OCT-07
 
 
 
 
SQL>
SQL> SELECT * FROM ALL_CONS_COLUMNS
  2  WHERE CONSTRAINT_NAME = 'XCL01200'
  3  AND OWNER = 'RT_TEST2';
 
RT_TEST2                       XCL01200
SERVICE_LINE
SERVICE_NUMBER
         2
 
RT_TEST2                       XCL01200
SERVICE_LINE
SEQUENCE_NUMBER
         3
 
RT_TEST2                       XCL01200
SERVICE_LINE
FK_CLAIM_ID
 
         1
 

0
rmtye
Asked:
rmtye
1 Solution
 
dqmqCommented:
You did most of the work.  This SQL shows that the constraint is on table "SVC_LIN_EX_CD_ASSN"

SQL> select * from all_constraints
  2  WHERE CONSTRAINT_NAME = 'CCL0CF00'
  3  AND OWNER = 'RT_TEST2';
 
RT_TEST2                       CCL0CF00                       R
SVC_LIN_EX_CD_ASSN

So this sql will show which columns are involved.  When you ran a similar query, you specified a table name that was incorrect:

SQL> SELECT * FROM ALL_CONS_COLUMNS
  2  WHERE CONSTRAINT_NAME = 'CCL0CF00'
  3  AND OWNER = 'RT_TEST2';


BTW, I think the update statement at the top of the listing is not the problem.  The problem appears to be inside the UPDATE_STATIC_DATA() procedure.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now