Unanswered: How to extract "Referential Integrity"
i'm working with a Oracle-DB where nobody can tell me about the ri. So i tried to crearte a script to get this information by my own. I've got:
select c.OWNER cown,
from all_constraints c,
where c.CONSTRAINT_TYPE = 'R'
and c.OWNER not in ('SYS','SYSTEM')
and c.R_OWNER = r.OWNER
and c.R_CONSTRAINT_NAME = r.CONSTRAINT_NAME
and c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
and c.OWNER = cc.OWNER
and r.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
and r.OWNER = rc.OWNER
and cc.POSITION = rc.POSITION
order by c.OWNER, c.TABLE_NAME, c.CONSTRAINT_NAME, cc.POSITION;
But here's me end. I need to knwo in which order do i have to insert data and vice versa to delete data.
In most cases:
Inserting: first insert master, then detail
Deleting: first delete detail, then master
i knwo, therefore i'm trying to bild a query to tell me the order in which table i have to insert first. The reverse order is the right one for deleting records.
What i'm looking for is a query result like this:
So i have to insert data first in "tab_a1", then in "tab_xx" and at last in "tab_99". But as you can see in my already posted query, that's not what i'm looking for. I'm looking for help to get this (or an other) query working like i want to.