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

deleting dependencies

Hi Experts,

I am trying to delete a row from DEMOGRAPHICS table, but it has integrity constraint. I need to delete dependencies first. I do see SQL error such as:
ORA-02292: integrity constraint (xxx_xxx.FK_xxx_xxx) violated - child record found.

How do I find child record and delete it?

I am using oracle SQL developer.

thx,
0
dkim18
Asked:
dkim18
3 Solutions
 
Javier MoralesOracle DBACommented:
You should have to change the integrity constraint to set it up as "ON DELETE CASCADE".

The easiest way is to query the parent table:

Select PK from parent_table where pk in (select fk from DEMOGRAPHICS where id=id_for_delete);

Open in new window


otherways, you can disable de constraint, delete the row and enable with NOVALIDATE option.

then, create a EXCEPTIONS table and run "alter table xxxx enable constraint fk_xxxx  EXCEPTIONS INTO EXCEPTIONS;"
and you will get all the rowid's of the parent table to delete.

create table exceptions(row_id rowid,
                      owner varchar2(30),
                      table_name varchar2(30),
                      constraint varchar2(30));

Open in new window


Hope this would help !
Regards,
0
 
slightwv (䄆 Netminder) Commented:
>>you can disable de constraint, delete the row and enable with NOVALIDATE option

Don't do this.  This will leave you with data integrity issues.  Not only with the orphan child row you create with the delete but possible with new rows added while the constraint is disabled.

Use the SQL above or look in user_constraints for a constraint_name 'FK_xxx_xxx' and see what table it referneces.
0
 
Javier MoralesOracle DBACommented:
well... I also DON'T recommend to use my second method unless you can garantee your database integrity is safe from other's... if your database is on production, with other concurrent sessions, well... you know the risks of disabling integrity constraints.

... the best way is to query the parent table (find it , as I mentioned above, but if you're the only one that access the data, and your integrity is under your control, you will find with this other second method quite interesting... for example, if there are many other FK's pointing to the parent table, you will get them all (with constraint_name, table_name and row_id's).
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
dkim18Author Commented:
I am trying to see what table it references, so I used this:

 
select patient_id from demographics where patient_id in (select FK_VISIT_NOTE_DEMOG from demographics where patient_id=7531);

Open in new window


But I got  this error.

SQL Error: ORA-00904: "FK_VISIT_NOTE_DEMOG": invalid identifier
0
 
slightwv (䄆 Netminder) Commented:
FK_VISIT_NOTE_DEMOG is the 'name' of the constraint.  You need to know the column name in the table to run that SQL.

That is a round-about way to get what you need.  You need to track down the table_name of FK_VISIT_NOTE_DEMOG.  then the FK column name, then delete those FK values where  patient_id=7531
0
 
Mark GeerlingsDatabase AdministratorCommented:
And, it is actually a child table, not a parent table, that contains the records that cause your ORA-02292 error.  You are actually trying to delete the parent table record when you get this error.

I use these two queries (in SQL*Plus) to gather information on related tables:

set linesize 140;
-- The decode on position offsets all but the first column of multi-column keys 1 space right
break on "Table name" skip 1 on "Reference table";
select substr(c.table_name,1,24) "Table name",
--c.constraint_name "Constraint",
substr(decode(cc1.position,1,'',' ')||cc1.column_name,1,20) "Foreign key column",
substr(r.table_name,1,24) "Reference table",
substr(decode(cc2.position,1,'',' ')||cc2.column_name,1,20) "Parent key col.",
c.delete_rule, c.status
from user_cons_columns cc1, user_cons_columns cc2, user_constraints r, user_constraints c
where c.constraint_type = 'R'
and r.constraint_name = c.r_constraint_name
and cc1.owner = c.owner
and cc1.constraint_name = c.constraint_name
and cc1.table_name = c.table_name
and cc2.owner = r.owner
and cc2.constraint_name = r.constraint_name
and cc2.table_name = r.table_name
and cc2.position = cc1.position
and c.table_name like upper('&table_name')
order by c.table_name, c.constraint_name, cc1.position;


column Cname format a50 heading Name;
-- added "union" for Oracle8.0.5, in Oracle7 these were included in all_dependencies
undefine referenced_object;
select substr(owner,1,12) "Owner", substr(name,1,50) "Cname", type,
referenced_type "Referenced obj.", null "Delete rule", null "Status"
from all_dependencies
where referenced_name = upper('&&referenced_object')
--and referenced_name <> 'STANDARD'
union
select substr(c.owner,1,12), substr(c.table_name||'.'||c.constraint_name,1,50), 'TABLE',
c.r_constraint_name, c.delete_rule, c.status
from all_constraints c
where (c.r_constraint_name,c.r_owner) in (select x.constraint_name,x.owner
from all_constraints x
where x.table_name in upper('&&referenced_object')
and x.constraint_type in ('P','U'))
order by 3,2;
undefine referenced_object;

Notes:
1. You won't need the "set...", "break...", 'Column..." and "Undefine..." lines if you use some other tool like TOAD or SQL Developer to run these "select..." statements.

2. This first one is the reverse of what you actually need, since it is from the child table to find the parent table, and your problem is the reverse.  You have the parent table name and you need to find the child table.  I think this second statemen though will get you close to what you need.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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