asked on

Oracle 12c Which are Parent tables and which tables are Child tables?

Hello Expert,

A large web-based application is connected to an Oracle 12c database.
The application has a feature which puts a record in Draft status to isolate the record while changes are made.
The Draft record is deleted when the record is Saved and Approved. Or it is supposed to be.
For some reason, under investigation, the Draft records are sometimes not deleted.
This create an invalid data state we lovingly call a Stray Draft.
A Stray Draft causes an error if a user ever tries to edit the record again, I think
because the new Draft record ties to use the same primary key as the old, undeleted draft.

That is the context.

So the boss says 'Delete all old (last_update_date > than timestamp - 90) drafts.
The only fly in the ointment is there are about eighty draft tables with name syntax
D_tablename and it is known by this writer that some of them have parent-child
relationships. That is, for instance, the D_PAYER table is Parent to the Child D_PAYER_ADDRESS
table and the D_PAYER_ADDRESS table is Parent to the Child D_PAYER_ADDRESS_TYPE and
the D_PAYER_ADDRESS_USAGE tables.
One cannot delete Child records that have a foreign key relationship to Parent
records.

With eight tables getting everything in order empirically is pretty tough. So
visiting with one of the developers, he said 'If you look at table meta data (Columns, Data Model,
Constraints, Grants, Statistics, Triggers, Flashback, Dependencies,
Details, Partitions, Indexes, SQL) for the table in
Oracle SQL Developer (18.3) the Parent Child can be discovered.

Sounded easy when he said it fast,

Looking at the meta data for D_PAYER_ADDRESS_TYPE in Oracle SQL Developer
there is Columns, Data Model, Constraints, Grants, Statistics, Triggers, Flashback, Dependencies,
Details, Partitions, Indexes, and SQL. I was guessed that the needed dependency info
would be in Dependencies. But the Dependencies tab a Type 'Synonym' and
'Package Body'. It was expected that a Foreign Key relationship would be found
and deleting a record that was a foreign key to a primary key in a Parent table
would be the operation to which Oracle would object.

Also had hope for 'Model' (Copy of the Model as a GIF copied herewith.) But this shows
the D_PAYER_ADDRESS_TYPE primary key as PAYER_ADDRESS_TYPE_ID but no
foreign keys.

Looked all the other meta data tabs, Columns, Constraints, Grants,
Statistics, Triggers, Flashback, et.al. but no luck.

Is there a way to see which tables are Parent tables to Child tables
and what the Child tables are?

Thanks.

Allen in Dallas
D_PAYER_ADDRESS_TYPE_Model.gif

D B

Found this on Stack Exchange. I'm a SQL Server guy; been some time since I've worked with Oracle.

SELECT
    CONST.NAME AS CONSTRAINT_NAME,
    RCONST.NAME AS REF_CONSTRAINT_NAME,

    OBJ.NAME AS TABLE_NAME,
    COALESCE(ACOL.NAME, COL.NAME) AS COLUMN_NAME,
    CCOL.POS# AS POSITION,

    ROBJ.NAME AS REF_TABLE_NAME,
    COALESCE(RACOL.NAME, RCOL.NAME) AS REF_COLUMN_NAME,
    RCCOL.POS# AS REF_POSITION
FROM SYS.CON$ CONST
INNER JOIN SYS.CDEF$ CDEF ON CDEF.CON# = CONST.CON#
INNER JOIN SYS.CCOL$ CCOL ON CCOL.CON# = CONST.CON#
INNER JOIN SYS.COL$ COL  ON (CCOL.OBJ# = COL.OBJ#) AND (CCOL.INTCOL# = COL.INTCOL#)
INNER JOIN SYS.OBJ$ OBJ ON CCOL.OBJ# = OBJ.OBJ#
LEFT JOIN SYS.ATTRCOL$ ACOL ON (CCOL.OBJ# = ACOL.OBJ#) AND (CCOL.INTCOL# = ACOL.INTCOL#)

INNER JOIN SYS.CON$ RCONST ON RCONST.CON# = CDEF.RCON#
INNER JOIN SYS.CCOL$ RCCOL ON RCCOL.CON# = RCONST.CON#
INNER JOIN SYS.COL$ RCOL  ON (RCCOL.OBJ# = RCOL.OBJ#) AND (RCCOL.INTCOL# = RCOL.INTCOL#)
INNER JOIN SYS.OBJ$ ROBJ ON RCCOL.OBJ# = ROBJ.OBJ#
LEFT JOIN SYS.ATTRCOL$ RACOL  ON (RCCOL.OBJ# = RACOL.OBJ#) AND (RCCOL.INTCOL# = RACOL.INTCOL#)

WHERE CONST.OWNER# = userenv('SCHEMAID')
  AND RCONST.OWNER# = userenv('SCHEMAID')
  AND CDEF.TYPE# = 4  /* 'R' Referential/Foreign Key */;

Open in new window

ASKER CERTIFIED SOLUTION

D B

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

You should post the links when posting content from the Internet:
http://support.experts-exchange.com/customer/portal/articles/1162518

Personally I like the solution from Tom Kyte:
https://asktom.oracle.com/pls/apex/asktom.search?tag=all-parent-child-tables-in-the-database

A better solution to the question is once you have found all your parent child relationships, change them to cascade on delete. Then you can delete from a parent table and the child rows are automatically removed.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/constraint.html#GUID-1055EA97-BA6F-4764-A15F-1024FD5B6DFE

ON DELETE Clause

The ON DELETE clause lets you determine how Oracle Database automatically maintains referential integrity if you remove a referenced primary or unique key value. If you omit this clause, then Oracle does not allow you to delete referenced key values in the parent table that have dependent rows in the child table.

Specify CASCADE if you want Oracle to remove dependent foreign key values.

flow01

I also would expect a foreign_key constraint to be seen , but I'm using Oracle PL/SQL developer so I can't tell for SQL developer.

You can also check the dba_constraints, all_constraints or user_constraint views. For a foreign_key constraint the constraint_type = 'R' , owner and table_name define the child and r_owner , r_constraint_name refer to the uniq identifitable_name the parent .
for example

select c.constraint_name, c.table_name child, p.table_name parent
from all_constraints p, all_constraints c
where p.constraint_type in ('P','U') -- uniq constraint of the parent
and c.constraint_type in ('R') -- foreign key constraint of the child
AND p.owner = c.r_owner
AND p.constraint_name = c.r_constraint_name
--and c.owner = '...' and c.table_name like 'D%'
/

If you don't find it there check the triggers on the parent table : maybe there is a trigger firing with a delete that checks for existence of child records (and on the child table an on insert trigger that checks for the existence of the parent)

slightwv (䄆 Netminder)

Good point of the trigger.

When you try to delete a for from D_PAYER_ADDRESS that has a value in D_PAYER_ADDRESS_TYPE, what is the exact error you receive?

If it is a ORA-02292 then I too would expect to see the FK in the Model image you posted. If it is a different error, post it and we'll go from there.

PatHartman

I'm not sure how finding the relationships will help you.

Once you manage to delete the bad data, are there any plans to modify the bad design or find and fix the bad programming logic that allows this to happen? If not, plan on this being a monthly task.

If you can't find the bad code or the problem isn't simply bad code but a bad design, you will need to make this a process that runs regularly to perform cleanup so you should develop with that in mind.

slightwv (䄆 Netminder)

A trigger could be a quick stop gap solution until you find and fix the code.

Mark Geerlings

It is possible that the original developers of this program never created the actual foreign key relationships in the database, but instead relied on the (wonderfully thorough, or not) application code they wrote to try to keep the database records consistent, without using the database feature (foreign keys) that could have made this job much easier.

And if they never declared these foreign keys in the database, there is no single query that you can run now, that will automatically display these table relationships to you.

If you are able to determine these table relationships, you could consider adding your own foreign keys in the database. But, if the application code was written to not expect these foreign keys, adding them now could cause problems in the application.

Allen Pitts

ASKER

The first query from Bishop did not work probably because is could not figure out how to adapt it to the local environment. But the second one,

1: SELECT ac.table_name,
2: column_name,

worked very well. Thanks.

Also the Tom Kyte solution from SlightVW was good.

Thanks.