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.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Javier MoralesOwnerCommented:
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 !
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.
Javier MoralesOwnerCommented:
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).
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
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
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'
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;

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.