Monday, August 6, 2012
ORA-02291: integrity constraint (string.string) violated - parent key not found
ORA-02291: integrity constraint (string.string) violated - parent key not found
Cause: A foreign key value has no matching primary key value.
Action: Delete the foreign key or add a matching primary key.
For an insert statement, this ORA-02291 error is common when you are trying to insert a child without a matching parent, as defined by a foreign key constraint. In that case, you need to add the parent row to the table and then re-insert your child table row.
SELECT DISTINCT uc.constraint_name||CHR(10)
|| '('||ucc1.TABLE_NAME||'.'||ucc1.column_name||')' constraint_source
, 'REFERENCES'||CHR(10)
|| '('||ucc2.TABLE_NAME||'.'||ucc2.column_name||')' references_column
FROM user_constraints uc
, user_cons_columns ucc1
, user_cons_columns ucc2
WHERE uc.constraint_name = ucc1.constraint_name
AND uc.r_constraint_name = ucc2.constraint_name
And Uc.Constraint_Type = 'R'
----AND uc.constraint_name = UPPER('&input_constraint_name');
AND uc.constraint_name = 'FK_CHARGESCLIENT';
Select Distinct Cfirmnumber,Cpostingaccount From Tblclientchargesdetail
Minus
Select Distinct FIRMNUMBER,OOWNCODE From ACCOUNTS;
add the missing row into table.
---------------------------------------------------------------------------------------------------------------------------------------
Select 'SELECT DISTINCT ' || Columns1 || ' from ' ||tablename || ' minus ' || 'SELECT DISTINCT ' || Columns2 || ' from ' || r_table_name || ';'
From
(
select tablename,r_table_name,
cname1 || nvl2(cname2,','||cname2,null) ||
nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
Nvl2(Cname7,','||Cname7,Null) || Nvl2(Cname8,','||Cname8,Null)
Columns1,
cname9 || nvl2(cname10,','||cname10,null) ||
Nvl2(Cname11,','||Cname11,Null) || Nvl2(Cname12,','||Cname12,Null) ||
Nvl2(Cname13,','||Cname13,Null) || Nvl2(Cname14,','||Cname14,Null) ||
Nvl2(Cname15,','||Cname15,Null) || Nvl2(Cname16,','||Cname16,Null) Columns2
From (
Select B.Table_Name Tablename,
b.Constraint_Name,
Max(Decode( A.Position, 1, A.Column_Name, Null )) Cname1,
Max(Decode( A.Position, 2, A.Column_Name, Null )) Cname2,
Max(Decode( A.Position, 3, A.Column_Name, Null )) Cname3,
Max(Decode( A.Position, 4, A.Column_Name, Null )) Cname4,
Max(Decode( A.Position, 5, A.Column_Name, Null )) Cname5,
Max(Decode( A.Position, 6, A.Column_Name, Null )) Cname6,
Max(Decode( A.Position, 7, A.Column_Name, Null )) Cname7,
Max(Decode( A.Position, 8, A.Column_Name, Null )) Cname8,
c.Table_Name r_table_name,
Max(Decode( C.Position, 1, C.Column_Name, Null )) Cname9,
Max(Decode( C.Position, 2, C.Column_Name, Null )) Cname10,
Max(Decode( C.Position, 3, C.Column_Name, Null )) Cname11,
Max(Decode( C.Position, 4, C.Column_Name, Null )) Cname12,
Max(Decode( C.Position, 5, C.Column_Name, Null )) Cname13,
Max(Decode( C.Position, 6, C.Column_Name, Null )) Cname14,
Max(Decode( C.Position, 7, C.Column_Name, Null )) Cname15,
Max(Decode( C.Position, 8, C.Column_Name, Null )) Cname16
From User_Cons_Columns a,user_constraints b,User_Cons_Columns c
Where A.Constraint_Name = B.Constraint_Name
And B.R_Constraint_Name=C.Constraint_Name
And B.Constraint_Type = 'R'
And B.Constraint_Name='FK_CHARGESCLIENT'
Group By B.Table_Name, B.Constraint_Name,C.Table_Name
));
-----------------------------------
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment