Oracle Primary key Foreign Key report
oracle pk key fk key
Oracle pk fk
Listing Table Dependencies ( Foreign Key References )
https://anuj-singh.blogspot.com/2025/ ORA-02292: integrity constraint violated - child record found=======================
set feed off
set pagesize 10000
ttitle off
--
ttitle left'primary and foreign key relationsships' skip 2
set feed off
set pagesize 10000
--
column datum new_value datum noprint
column for_owner format a5 heading 'table|owner'
column pri_tsname format a10 heading 'tablespace'
column for_table format a17 heading 'from|foreign|table'
column for_col format a16 heading 'from|foreign|column'
column pri_table format a17 heading 'to|primary|table'
column pri_col format a16 heading 'to|primary|column'
break on for_owner skip 1
select
a.owner for_owner,
e.tablespace_name pri_tsname,
a.table_name for_table,
c.column_name for_col,
b.table_name pri_table,
d.column_name pri_col
from dba_constraints a,
dba_constraints b,
dba_cons_columns c,
dba_cons_columns d,
dba_tables e
where a.owner in ('SCOTT') ------- <<<<<<<<<<<<<<
and a.r_constraint_name = b.constraint_name
and a.constraint_type = 'R'
and b.constraint_type = 'P'
and a.r_owner = b.owner
and a.constraint_name = c.constraint_name
and a.owner = c.owner
and a.table_name = c.table_name
and b.constraint_name = d.constraint_name
and b.owner = d.owner
and b.table_name = d.table_name
and b.table_name = e.table_name
order by a.owner,a.table_name;
primary and foreign key relationsships
from from to to
table foreign foreign primary primary
owner tablespace table column table column
----- ---------- ----------------- ---------------- ----------------- ----------------
SCOTT EXAMPLE ORDER_LINES ORDER_ID ORDERS ID
USERS ORDER_LINES ORDER_ID ORDERS ID
SYSTEM ORDER_LINES ORDER_ID ORDERS ID
=================================
CLEAR COLUMNS
CLEAR BREAK
SET PAUSE ON
SET FEEDBACK OFF
SET PAGESIZE 20
COLUMN "This Table References Fk" FORMAT A30
COLUMN "This Table Pk" FORMAT A30
BREAK ON "This Table References Fk" SKIP 2
SPOOL TAB_DEPEND.txt
SELECT A.TABLE_NAME "This Table References Fk", DECODE( A.R_OWNER, A.OWNER, B.TABLE_NAME, A.R_OWNER || '.' || B.TABLE_NAME ) "This Table Pk"
FROM USER_CONSTRAINTS A, USER_CONSTRAINTS B
WHERE A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND A.CONSTRAINT_TYPE ='R'
AND B.CONSTRAINT_TYPE IN ( 'U', 'P' )
GROUP BY A.TABLE_NAME,DECODE( A.R_OWNER, A.OWNER, B.TABLE_NAME,A.R_OWNER || '.' || B.TABLE_NAME )
ORDER BY 1, 2
/
SQL> connect scott/tiger
Connected.
SQL> ed pkfk1
SQL> @pkfk1
primary and foreign key relationsships
This Table References Fk This Table Pk
------------------------------ ------------------------------
ORDER_LINES ORDERS
===============================================
set linesize 300 pagesize 300
col FK_TABLE for a20
col FK_COLUMN for a20
col PK_TABLE for a24
col PK_COLUMN for a24
col CONSTRAINT_NAME for a20
col owner for a15
WITH
reference_view AS
(SELECT a.owner, a.table_name, a.constraint_name, a.constraint_type, a.r_owner, a.r_constraint_name, b.column_name
FROM dba_constraints a, dba_cons_columns b
WHERE
a.owner = b.owner
AND a.constraint_name = b.constraint_name
AND constraint_type = 'R'),
constraint_view AS
(SELECT a.owner a_owner, a.table_name, a.column_name, b.owner b_owner, b.constraint_name
FROM dba_cons_columns a, dba_constraints b
WHERE a.owner = b.owner
AND a.constraint_name = b.constraint_name
AND b.constraint_type = 'P'
) ,
usertableviewlist AS
(
select TABLE_NAME from dba_tables
)
SELECT
distinct rv.owner, rv.table_name FK_Table , rv.column_name FK_Column , CV.table_name PK_Table , rv.column_name PK_Column , rv.r_constraint_name Constraint_Name
FROM reference_view rv, constraint_view CV , usertableviewlist UTable
WHERE rv.r_constraint_name = CV.constraint_name
AND rv.r_owner = CV.b_owner
And UTable.TABLE_NAME = rv.table_name
and rv.column_name='DEPTNO'
and rv.owner='SYS'
;
OWNER FK_TABLE FK_COLUMN PK_TABLE PK_COLUMN CONSTRAINT_NAME --------------- -------------------- -------------------- ------------------------ ------------------------ -------------------- SYS EMP1 DEPTNO DEPT DEPTNO PK_DEPT SYS EMP DEPTNO DEPT DEPTNO PK_DEPT SYS EMP2 DEPTNO DEPT DEPTNO PK_DEPT
set linesize 300
col PKTABLE_SCHEM for a15
col PKTABLE_NAME for a15
col PKCOLUMN_NAME for a15
col FKTABLE_NAME for a15
col FKTABLE_SCHEM for a15
col FKCOLUMN_NAME for a15
col FK_NAME for a15
col PK_NAME for a15
SELECT NULL AS pktable_cat,
p.owner as pktable_schem,
p.table_name as pktable_name,
pc.column_name as pkcolumn_name,
NULL as fktable_cat,
f.owner as fktable_schem,
f.table_name as fktable_name,
fc.column_name as fkcolumn_name,
fc.position as key_seq,
NULL as update_rule,
decode (f.delete_rule, 'CASCADE', 0, 'SET NULL', 2, 1) as delete_rule,
f.constraint_name as fk_name,
p.constraint_name as pk_name,
decode(f.deferrable, 'DEFERRABLE',5 ,'NOT DEFERRABLE',7 , 'DEFERRED', 6 ) deferrability
FROM all_cons_columns pc, all_constraints p,
all_cons_columns fc, all_constraints f
WHERE 1 = 1
AND f.table_name = 'EMP' ----<<<
AND f.owner = 'SYS' ---<<<
AND f.constraint_type = 'R'
AND p.owner = f.r_owner
AND p.constraint_name = f.r_constraint_name
AND p.constraint_type = 'P' -- This is the problem
AND pc.owner = p.owner
AND pc.constraint_name = p.constraint_name
AND pc.table_name = p.table_name
AND fc.owner = f.owner
AND fc.constraint_name = f.constraint_name
AND fc.table_name = f.table_name
AND fc.position = pc.position
ORDER BY pktable_schem, pktable_name, key_seq
P PKTABLE_SCHEM PKTABLE_NAME PKCOLUMN_NAME F FKTABLE_SCHEM FKTABLE_NAME FKCOLUMN_NAME KEY_SEQ U DELETE_RULE FK_NAME PK_NAME DEFERRABILITY
- --------------- --------------- --------------- - --------------- --------------- --------------- ---------- - ----------- --------------- --------------- -------------
SYS DEPT DEPTNO SYS EMP DEPTNO 1 1 FK_DEPTNO PK_DEPT 7
========================================
col TABLE_NAME for a15
col COLUMN_NAME for a15
col R_TABLE_NAME for a15
col R_OWNER for a15
col R_COLUMN_NAME for a15
col R_CONSTRAINT_NAME for a15
with cons as
(select c.owner, c.constraint_name, c.constraint_type, c.table_name, c.r_owner, c.r_constraint_name, cc.column_name, cc.position
from dba_constraints c, dba_cons_columns cc
where (Not ((c.constraint_name like 'BIN$%==$0') and length(c.constraint_name) = 30))
and ( c.constraint_type = 'R'
-- and ((c.owner = sys_context('USERENV', 'CURRENT_SCHEMA')) or (c.r_owner = sys_context('USERENV', 'CURRENT_SCHEMA')))
--and c.owner='SYS'
and c.owner = cc.owner
and c.constraint_name = cc.constraint_name
)
or ( c.constraint_type in ('P', 'U')
-- and c.owner = sys_context('USERENV', 'CURRENT_SCHEMA')
and c.owner = cc.owner
and c.constraint_name = cc.constraint_name))
select
'REF_INTEGRITY' AS type,
ref_cons.owner,
ref_cons.table_name,
ref_cons.column_name,
ref_cons.constraint_name,
pk_cons.owner as r_owner,
pk_cons.table_name AS r_table_name,
pk_cons.column_name AS r_column_name,
pk_cons.constraint_name AS r_constraint_name
FROM cons ref_cons,
cons pk_cons
WHERE ref_cons.constraint_type = 'R'
and pk_cons.constraint_type in ('P', 'U')
and pk_cons.owner in (ref_cons.r_owner, ref_cons.owner)
AND ref_cons.r_constraint_name = pk_cons.constraint_name
AND ref_cons.position = pk_cons.position
and pk_cons.table_name='DEPT'
and pk_cons.owner='SYS'
ORDER BY ref_cons.owner,
ref_cons.table_name,
ref_cons.constraint_name,
ref_cons.position;
TYPE OWNER TABLE_NAME COLUMN_NAME CONSTRAINT_NAME R_OWNER R_TABLE_NAME R_COLUMN_NAME R_CONSTRAINT_NA
------------- --------------- --------------- --------------- -------------------- --------------- --------------- --------------- ---------------
REF_INTEGRITY SYS EMP DEPTNO FK_DEPTNO SYS DEPT DEPTNO PK_DEPT
REF_INTEGRITY SYS EMP1 DEPTNO FK_DEPTNO1 SYS DEPT DEPTNO PK_DEPT
REF_INTEGRITY SYS EMP2 DEPTNO FK_DEPTNO2 SYS DEPT DEPTNO PK_DEPT
====
set linesize 300
col XCOLUMNS for a20
col R_COLUMNS for a20
col type for a8
col TABLE_NAME for a29
select rcon.owner as r_owner, rcon.constraint_name as r_constraint_name, rcon.table_name as r_table_name,
listagg (rcol.column_name, ', ') WITHIN GROUP (ORDER BY rcol.owner, rcol.table_name, rcol.constraint_name) R_COLUMNS,
rcon.constraint_type as type, con.owner, con.table_name, con.constraint_name,
listagg (col.column_name, ', ') WITHIN GROUP (ORDER BY col.owner, col.table_name, col.constraint_name) XCOLUMNS
from all_constraints rcon
join all_cons_columns rcol on rcol.owner=rcon.owner and rcol.table_name=rcon.table_name and rcol.constraint_name=rcon.constraint_name
left join all_constraints con on rcon.owner = con.r_owner and rcon.constraint_name = con.r_constraint_name
left join all_cons_columns col on col.owner=con.owner and col.table_name=con.table_name and col.constraint_name=con.constraint_name
and rcol.position = col.position
where rcon.owner = upper('XXXX')
and rcon.table_name = upper('TTTTTT')
--and rcon.constraint_type in ('P','U')
--and rcon.constraint_name = nvl(upper(p_constraint),rcon.constraint_name)
--and rcon.status = 'ENABLED'
group by rcon.owner, rcon.constraint_name, rcon.table_name, rcon.constraint_type,con.owner, con.table_name, con.constraint_name
order by rcon.owner, rcon.constraint_name, rcon.constraint_type;
define O='ANUJ' define T='DEPT' SET linesize 300 pagesize 200 COL PARENT_TABLE FOR A25 COL CHILD_TABLE FOR A25 COL CHILD_FK_COLUMN FOR A25 COL PARENT_PK_COLUMN FOR A25 COL FK_CONSTRAINT_NAME FOR A30 COL PK_CONSTRAINT_NAME FOR A30 COL PARENT_OWNER FOR A15 COL CHILD_OWNER FOR A15 COL LEVEL_NO FOR 999 WITH recursive_fks ( parent_table, child_table, child_fk_column, parent_pk_column, fk_constraint_name, pk_constraint_name, parent_owner, child_owner, level_no ) AS ( -- Level 0: direct children of starting table SELECT ac_parent.table_name AS parent_table, ac_child.table_name AS child_table, acc_child.column_name AS child_fk_column, acc_parent.column_name AS parent_pk_column, ac_child.constraint_name AS fk_constraint_name, ac_parent.constraint_name AS pk_constraint_name, ac_parent.owner AS parent_owner, ac_child.owner AS child_owner, 0 AS level_no FROM all_constraints ac_child JOIN all_cons_columns acc_child ON ac_child.constraint_name = acc_child.constraint_name AND ac_child.owner = acc_child.owner JOIN all_constraints ac_parent ON ac_child.r_constraint_name = ac_parent.constraint_name AND ac_child.r_owner = ac_parent.owner JOIN all_cons_columns acc_parent ON ac_parent.constraint_name = acc_parent.constraint_name AND ac_parent.owner = acc_parent.owner AND acc_parent.position = acc_child.position WHERE ac_parent.table_name = '&T' AND ac_parent.owner = '&O' AND ac_child.constraint_type = 'R' UNION ALL -- Recursive: follow relationships downwards SELECT ac_parent.table_name, ac_child.table_name, acc_child.column_name, acc_parent.column_name, ac_child.constraint_name, ac_parent.constraint_name, ac_parent.owner, ac_child.owner, r.level_no + 1 FROM all_constraints ac_child JOIN all_cons_columns acc_child ON ac_child.constraint_name = acc_child.constraint_name AND ac_child.owner = acc_child.owner JOIN all_constraints ac_parent ON ac_child.r_constraint_name = ac_parent.constraint_name AND ac_child.r_owner = ac_parent.owner JOIN all_cons_columns acc_parent ON ac_parent.constraint_name = acc_parent.constraint_name AND ac_parent.owner = acc_parent.owner AND acc_parent.position = acc_child.position JOIN recursive_fks r ON ac_parent.table_name = r.child_table AND ac_parent.owner = r.child_owner WHERE ac_child.constraint_type = 'R' ) SELECT DISTINCT parent_table, child_table, child_fk_column, parent_pk_column, fk_constraint_name, pk_constraint_name, parent_owner, child_owner, level_no FROM recursive_fks ORDER BY level_no, parent_table, child_table, child_fk_column; PARENT_TABLE CHILD_TABLE CHILD_FK_COLUMN PARENT_PK_COLUMN FK_CONSTRAINT_NAME PK_CONSTRAINT_NAME PARENT_OWNER CHILD_OWNER LEVEL_NO ------------------------- ------------------------- ------------------------- ------------------------- ------------------------------ ------------------------------ --------------- --------------- -------- DEPT EMP DEPTNO DEPTNO FK_DEPTNO PK_DEPT ANUJ ANUJ 0define O='ANUJ1' --define T='DEPT' define T='EMP' set linesize 300 col XCOLUMNS for a20 col R_COLUMNS for a20 col type for a8 col TABLE_NAME for a15 col R_OWNER for a15 col R_TABLE_NAME for a15 select rcon.owner as r_owner, rcon.constraint_name as r_constraint_name, rcon.table_name as r_table_name, listagg (rcol.column_name, ', ') WITHIN GROUP (ORDER BY rcol.owner, rcol.table_name, rcol.constraint_name) R_COLUMNS, rcon.constraint_type as type, con.owner, con.table_name, con.constraint_name, listagg (col.column_name, ', ') WITHIN GROUP (ORDER BY col.owner, col.table_name, col.constraint_name) XCOLUMNS from all_constraints rcon join all_cons_columns rcol on rcol.owner=rcon.owner and rcol.table_name=rcon.table_name and rcol.constraint_name=rcon.constraint_name left join all_constraints con on rcon.owner = con.r_owner and rcon.constraint_name = con.r_constraint_name left join all_cons_columns col on col.owner=con.owner and col.table_name=con.table_name and col.constraint_name=con.constraint_name and rcol.position = col.position where rcon.owner = upper('&O') and rcon.table_name = upper('&T') --and rcon.constraint_type in ('P','U') --and rcon.constraint_name = nvl(upper(p_constraint),rcon.constraint_name) --and rcon.status = 'ENABLED' group by rcon.owner, rcon.constraint_name, rcon.table_name, rcon.constraint_type,con.owner, con.table_name, con.constraint_name order by rcon.owner, rcon.constraint_name, rcon.constraint_type; R_OWNER R_CONSTRAINT_NAME R_TABLE_NAME R_COLUMNS TYPE OWNER TABLE_NAME CONSTRAINT_NAME XCOLUMNS --------------- -------------------- --------------- -------------------- -------- --------------- --------------- ------------------------------ -------------------- ANUJ1 PK_DEPT DEPT DEPTNO P ANUJ1 EMP FK_DEPTNO DEPTNO 1 row selected. ===== define O='ANUJ1' --define T='DEPT' define T='EMP' R_OWNER R_CONSTRAINT_NAME R_TABLE_NAME R_COLUMNS TYPE OWNER TABLE_NAME CONSTRAINT_NAME XCOLUMNS --------------- -------------------- --------------- -------------------- -------- --------------- --------------- ------------------------------ -------------------- ANUJ1 FK_DEPTNO EMP DEPTNO R ANUJ1 PK_EMP EMP EMPNO P 2 rows selected.SET LINESIZE 200 PAGESIZE 200 LONG 100000 SET TRIMOUT ON TRIMSPOOL ON COLUMN fk_script FORMAT A120 WORD_WRAP col t1_owner for a12 col t2_owner for a12 select fk_script from ( select fk_script,t1_owner,t2_owner from ( SELECT DISTINCT 'ALTER TABLE ' || t1.t1_owner || '.' || t1.t1_table_name || ' ADD CONSTRAINT ' || t1.t1_constraint_name || ' FOREIGN KEY (' || t1.t1_column_names || ')' || ' REFERENCES ' || t2.t2_owner || '.' || t2.t2_table_name || '(' || t2.t2_column_names || ');' AS fk_script,t1.t1_owner,t2.t2_owner FROM ( SELECT a.owner AS t1_owner, a.table_name AS t1_table_name, a.constraint_name AS t1_constraint_name, b.r_constraint_name AS t2_constraint_name, LISTAGG(a.column_name, ', ') WITHIN GROUP (ORDER BY a.position) AS t1_column_names FROM dba_cons_columns a JOIN dba_constraints b ON a.constraint_name = b.constraint_name AND a.owner = b.owner WHERE 1=1 -- b.constraint_type = 'R' --AND a.owner='ANUJ1' --and a.table_name='DEPT' GROUP BY a.owner, a.table_name, a.constraint_name, b.r_constraint_name ) t1 JOIN ( SELECT a.owner AS t2_owner, a.constraint_name AS t2_constraint_name, a.table_name AS t2_table_name, LISTAGG(a.column_name, ', ') WITHIN GROUP (ORDER BY a.position) AS t2_column_names FROM dba_cons_columns a JOIN dba_constraints b ON a.constraint_name = b.constraint_name AND a.owner = b.owner WHERE 1=1 --b.constraint_type IN ('P', 'U') --AND a.owner='ANUJ1' --and a.table_name='DEPT' GROUP BY a.owner, a.table_name, a.constraint_name ) t2 ON t1.t2_constraint_name = t2.t2_constraint_name where 1=1 --and t1.t1_owner='ANUJ1' --and t2.t2_table_name=t1.t1_table_name --and t1.t1_owner=t2.t2_owner --ORDER BY t1.t1_owner, t1.t1_table_name ) where 1=1 and t1_owner='ANUJ1' and t2_owner='ANUJ1' ) ;FK_SCRIPT ------------------------------------------------------------------------------------------------------------------------ ALTER TABLE ANUJ1.EMP ADD CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES ANUJ1.DEPT(DEPTNO); ALTER TABLE ANUJ1.EMP1 ADD CONSTRAINT FK_DEPTNO1 FOREIGN KEY (DEPTNO) REFERENCES ANUJ1.DEPT(DEPTNO);Disable Constraints: SET SERVEROUTPUT ON DECLARE CURSOR cur_const_disable_1 IS SELECT 'alter table ' || owner || '.' || table_name || ' disable constraint ' || constraint_name || ' ' text FROM dba_constraints WHERE owner IN ('ANUJ1') AND constraint_type IN ('R'); disable_1 cur_const_disable_1%rowtype; CURSOR cur_const_disable_2 IS SELECT 'alter table ' || cons.owner || '.' ||cons.table_name || ' disable constraint ' || constraint_name || ' ' text FROM dba_constraints cons, dba_tables tabs WHERE cons.owner=tabs.owner AND cons.table_name=tabs.table_name AND iot_type is null AND cons.owner IN ('ANUJ1') AND constraint_type IN ('P','U'); disable_2 cur_const_disable_2%rowtype; BEGIN dbms_output.put_line(sysdate || ':Disabling constraints R'); FOR disable_1 IN cur_const_disable_1 LOOP dbms_output.put_line(disable_1.text); --EXECUTE IMMEDIATE disable_1.text; END LOOP; dbms_output.put_line(sysdate || ':Disabling constraints P,U'); FOR disable_2 IN cur_const_disable_2 LOOP dbms_output.put_line(disable_2.text); -- EXECUTE IMMEDIATE disable_2.text; END LOOP; END; /14-NOV-25:Disabling constraints R alter table ANUJ1.EMP disable constraint FK_DEPTNO alter table ANUJ1.EMP1 disable constraint FK_DEPTNO1 14-NOV-25:Disabling constraints P,U alter table ANUJ1.DEPT disable constraint PK_DEPT alter table ANUJ1.EMP1 disable constraint PK_EMP1 alter table ANUJ1.EMP disable constraint PK_EMP PL/SQL procedure successfully completed.-- Enable Constraints --novalidate SET SERVEROUTPUT ON DECLARE CURSOR cur_const_enable_1 IS SELECT 'alter table ' || owner || '.' || table_name || ' enable novalidate constraint ' || constraint_name || ' ' text FROM dba_constraints WHERE owner IN ('ANUJ1') AND constraint_type IN ('P','U'); enable_1 cur_const_enable_1%rowtype; CURSOR cur_const_enable_2 IS SELECT 'alter table ' || owner || '.' || table_name || ' enable novalidate constraint ' || constraint_name || ' ' text FROM dba_constraints WHERE owner IN ('ANUJ1') AND constraint_type IN ('R'); enable_2 cur_const_enable_2%rowtype; BEGIN dbms_output.put_line(sysdate || ':Enabling constraints P,U'); FOR enable_1 IN cur_const_enable_1 LOOP dbms_output.put_line(enable_1.text); -- EXECUTE IMMEDIATE enable_1.text; END LOOP; dbms_output.put_line(sysdate || ':Enabling constraints R'); FOR enable_2 IN cur_const_enable_2 LOOP dbms_output.put_line(enable_2.text); --EXECUTE IMMEDIATE enable_2.text; END LOOP; END; /14-NOV-25:Enabling constraints P,U alter table ANUJ1.DEPT enable novalidate constraint PK_DEPT alter table ANUJ1.EMP1 enable novalidate constraint PK_EMP1 alter table ANUJ1.EMP enable novalidate constraint PK_EMP 14-NOV-25:Enabling constraints R alter table ANUJ1.EMP enable novalidate constraint FK_DEPTNO alter table ANUJ1.EMP1 enable novalidate constraint FK_DEPTNO1 PL/SQL procedure successfully completed.SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON BEGIN DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true); DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true); END; / define tab='DEPT' define O='ANUJ1' SELECT DBMS_METADATA.get_ddl ('TABLE', TABLE_name, owner) FROM all_tables WHERE 1=1 and owner = UPPER('&O') AND table_name = '&tab' ; SELECT DBMS_METADATA.get_ddl ('INDEX', index_name, owner) FROM all_indexes WHERE 1=1 and owner = UPPER('&O') AND table_name = '&tab' ;set linesize 300 pagesize 300 col SCHEMA_NAME for a12 col CONSTRAINT_NAME for a30 col COLUMN_NAME for a15 col TABLE_NAME for a35 col CONSTRAINT for a30 select ctr.owner as schema_name, ctr.constraint_name, ctr.table_name, col.column_name, ctr.search_condition as constraint, ctr.status from sys.all_constraints ctr join sys.all_cons_columns col on ctr.owner = col.owner and ctr.constraint_name = col.constraint_name and ctr.table_name = col.table_name where 1=1 and ctr.owner in ('ANUJ1') and ctr.table_name='DEPT' --and STATUS!='ENABLED' order by ctr.owner, ctr.table_name, ctr.constraint_name;Check detail of Foreign Keyset linesize 300 pagesize 300 col owner for a15 col "FOREIGN KEY" for a20 col "DEPENDS ON" for a20 col P_TABLE_NAME for a15 col F_TABLE_NAME for a15 define O='ANUJ1' define T='DEPT' define T='EMP' SELECT distinct cp.owner owner,cp.table_name p_table_name,cp.constraint_name "DEPENDS ON", cf.table_name f_table_name ,cf.constraint_name "FOREIGN KEY",ccp.column_name, ccp.position FROM all_constraints cp, all_cons_columns ccp, all_constraints cf WHERE 1=1 AND cp.constraint_name = ccp.constraint_name AND cf.r_constraint_name = cp.constraint_name AND cf.r_constraint_name = ccp.constraint_name and cp.table_name = upper('&T') and cp.owner=upper('&O') /OWNER P_TABLE_NAME DEPENDS ON F_TABLE_NAME FOREIGN KEY COLUMN_NAME POSITION --------------- --------------- -------------------- --------------- -------------------- -------------------- ---------- ANUJ1 DEPT PK_DEPT EMP2 FK_DEPTNO2 DEPTNO 1 ANUJ1 DEPT PK_DEPT EMP FK_DEPTNO DEPTNO 1 ANUJ1 DEPT PK_DEPT EMP1 FK_DEPTNO1 DEPTNO 1define O='ANUJ1' define T='DEPT' col R_CONSTRAINT_NAME for a20 col STATUS for a15 col CONSTRAINT_TYPE for a15 col TABLE_NAME for a20 SELECT TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME,STATUS FROM DBA_CONSTRAINTS WHERE OWNER = '&O' AND CONSTRAINT_TYPE='R' AND R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='&T') union SELECT TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME,STATUS FROM DBA_CONSTRAINTS WHERE OWNER = '&O' --AND CONSTRAINT_TYPE='R' AND CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='&T') ;TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE R_CONSTRAINT_NAME STATUS -------------------- ------------------------------ --------------- -------------------- --------------- DEPT PK_DEPT P ENABLED EMP FK_DEPTNO R PK_DEPT ENABLED 2 rows selected.======================================set linesize 300 pagesize 300 col FK_COLUMNS for a25 col PK_COLUMNS for a25 col PARENT_TABLE for a25 col CHILD_TABLE for a30 col FK_CONSTRAINT for a30 col FK_STATUS for a15 col PK_STATUS for a15 WITH fk_raw AS ( SELECT fk_constraint, parent_table, child_table, LISTAGG(fk_col, ',') WITHIN GROUP (ORDER BY col_position) AS fk_columns, LISTAGG(pk_col, ',') WITHIN GROUP (ORDER BY col_position) AS pk_columns, fk_status, pk_status FROM ( SELECT ac_child.constraint_name AS fk_constraint, ac_parent.table_name AS parent_table, ac_child.table_name AS child_table, acc_child.column_name AS fk_col, acc_parent.column_name AS pk_col, acc_child.position AS col_position, ac_child.status AS fk_status, ac_parent.status AS pk_status, ROW_NUMBER() OVER ( PARTITION BY ac_child.constraint_name, acc_child.column_name, acc_parent.column_name ORDER BY acc_child.position ) AS rn FROM all_constraints ac_child JOIN all_constraints ac_parent ON ac_child.r_constraint_name = ac_parent.constraint_name AND ac_child.r_owner = ac_parent.owner JOIN all_cons_columns acc_child ON ac_child.constraint_name = acc_child.constraint_name AND ac_child.owner = acc_child.owner JOIN all_cons_columns acc_parent ON ac_parent.constraint_name = acc_parent.constraint_name AND acc_parent.owner = ac_parent.owner AND acc_parent.position = acc_child.position WHERE ac_child.constraint_type = 'R' AND ac_parent.owner = UPPER('ANUJ1') ) t WHERE rn = 1 GROUP BY fk_constraint, parent_table, child_table, fk_status, pk_status ) SELECT PARENT_TABLE , PK_STATUS,CHILD_TABLE,FK_STATUS,fk_CONSTRAINT , PK_COLUMNS,FK_COLUMNS FROM fk_raw where 1=1 --and FK_STATUS!='ENABLED' --and PARENT_TABLE not like 'XX%' ORDER BY parent_table, child_table, fk_constraint;from web !!!!CREATE OR REPLACE TYPE STRINGARRAY AS TABLE OF VARCHAR2(50); CREATE OR REPLACE FUNCTION get_child_tables ( ptable VARCHAR2, powner VARCHAR2 DEFAULT 'ANUJ1', plevel NUMBER DEFAULT 10 ) RETURN stringarray PIPELINED AS atname stringarray := stringarray(); vlevel NUMBER; vtname VARCHAR2(50); nindex NUMBER := 0; bprocessed BOOLEAN := FALSE; CURSOR c1 (powner_in VARCHAR2, ptable_in VARCHAR2, plevel_in NUMBER) IS SELECT LEVEL, LPAD(' ', (LEVEL - 1) * 4, ' ') || pt AS tname FROM ( SELECT a.owner w1, a.table_name pt, a.constraint_name c1, a.r_constraint_name r1, b.owner w2, b.table_name ct, b.constraint_name c2, b.r_constraint_name r2 FROM all_constraints a LEFT JOIN all_constraints b ON a.constraint_name = b.r_constraint_name AND a.owner = b.owner WHERE a.owner = UPPER(powner_in) AND a.constraint_type IN ('P','R') ) START WITH pt = UPPER(ptable_in) CONNECT BY PRIOR ct = pt AND LEVEL <= plevel_in; BEGIN atname.EXTEND; atname(1) := 'NOTHING'; OPEN c1(powner, ptable, plevel); LOOP FETCH c1 INTO vlevel, vtname; EXIT WHEN c1%NOTFOUND; bprocessed := FALSE; IF nindex > 1 AND atname(atname.LAST - 1) = vtname THEN bprocessed := TRUE; END IF; IF NOT bprocessed THEN nindex := nindex + 1; atname.EXTEND; atname(nindex) := vtname; PIPE ROW (vlevel || ' => ' || vtname); END IF; END LOOP; CLOSE c1; RETURN; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(SQLERRM); RETURN; END get_child_tables; / SELECT * FROM TABLE(get_child_tables('DEPT')); SELECT * FROM TABLE( get_child_tables('DEPT','ANUJ1',3)); SELECT * FROM TABLE(get_child_tables('DEPT')); COLUMN_VALUE -------------------------------------------------- 1 => DEPT 2 => EMP SQL> SELECT * FROM TABLE(get_child_tables('EMP')); COLUMN_VALUE -------------------------------------------------- 1 => EMP 1 => EMP SQL> SELECT * FROM TABLE( get_child_tables('DEPT','ANUJ1',3)); COLUMN_VALUE -------------------------------------------------- 1 => DEPT 2 => EMP SELECT * FROM TABLE( get_child_tables('DEPT','ANUJ1',5));====CREATE OR REPLACE TYPE STRINGARRAY AS TABLE OF VARCHAR2(50);CREATE OR REPLACE FUNCTION anuj1.get_child_tables ( ptable VARCHAR2, powner VARCHAR2 DEFAULT 'ANUJ1', plevel NUMBER DEFAULT 10 ) RETURN stringarray PIPELINED IS atname stringarray := stringarray (); vlevel NUMBER; vtname VARCHAR2 (50); nindex NUMBER := 0; bprocessed BOOLEAN := FALSE; CURSOR c1 (powner_in IN VARCHAR2, ptable_in VARCHAR2, plevel_in NUMBER) IS SELECT LEVEL, LPAD (' ', (LEVEL - 1) * 4, ' ') || pt AS tname FROM ( SELECT a.owner w1, a.table_name pt, a.constraint_name c1, a.r_constraint_name r1, b.owner w2, b.table_name ct, b.constraint_name c2, b.r_constraint_name r2 FROM all_constraints a LEFT JOIN all_constraints b ON a.constraint_name = b.r_constraint_name AND a.owner = b.owner WHERE a.owner = UPPER (powner_in) AND a.constraint_type IN ('P','R') ) START WITH pt = UPPER(ptable_in) CONNECT BY PRIOR ct = pt AND LEVEL <= plevel_in; BEGIN -- first element atname.EXTEND; atname(1) := 'NOTHING'; OPEN c1 (powner, ptable, plevel); LOOP FETCH c1 INTO vlevel, vtname; EXIT WHEN c1%NOTFOUND; bprocessed := FALSE; IF nindex > 1 AND atname(atname.LAST - 1) = vtname THEN bprocessed := TRUE; END IF; IF NOT bprocessed THEN nindex := nindex + 1; atname.EXTEND; atname(nindex) := vtname; PIPE ROW (vlevel || ' => ' || vtname); END IF; END LOOP; CLOSE c1; RETURN; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(SQLERRM); RETURN; END get_child_tables; / SELECT * FROM TABLE(anuj1.get_child_tables('DEPT', 'ANUJ1', 6)); SELECT * FROM TABLE(anuj1.get_child_tables('DEPT', 'ANUJ1', 6)); COLUMN_VALUE -------------------------------------------------- 1 => DEPT 2 => EMP***************************SET SERVEROUTPUT ON SIZE 1000000 DECLARE v_root_table VARCHAR2(200) := 'DEPT'; -- root table v_deptno NUMBER := 10; -- deptno filter v_count PLS_INTEGER := 0; v_user VARCHAR2(200) := 'SCOTT'; v_sql CLOB; v_rows NUMBER := 0; v_batch_size NUMBER := 1000; -- rows per batch BEGIN DBMS_OUTPUT.PUT_LINE('--- BATCH FK-SAFE DELETE START for ' || v_user || ' ---'); DBMS_OUTPUT.PUT_LINE('Root table: ' || v_root_table || ' DEPTNO = ' || v_deptno); DBMS_OUTPUT.PUT_LINE(''); FOR rec IN ( WITH RECURSIVE_TREE(child_table, parent_table, child_fk_column, parent_pk_column, lvl) AS ( -- anchor: direct children of the root SELECT cc.table_name, p.table_name, cc.column_name, pc.column_name, 1 FROM all_constraints p JOIN all_cons_columns pc ON pc.constraint_name = p.constraint_name AND pc.owner = p.owner JOIN all_constraints fk ON fk.r_constraint_name = p.constraint_name AND fk.owner = p.owner JOIN all_cons_columns cc ON cc.constraint_name = fk.constraint_name AND cc.owner = fk.owner WHERE p.table_name = v_root_table AND p.owner = v_user AND fk.constraint_type = 'R' UNION ALL -- recursive: find children of previously found child tables SELECT cc.table_name, fk_parent.table_name, cc.column_name, pc.column_name, rt.lvl + 1 FROM RECURSIVE_TREE rt JOIN all_constraints fk_parent ON fk_parent.table_name = rt.child_table AND fk_parent.owner = v_user JOIN all_constraints fk ON fk.r_constraint_name = fk_parent.constraint_name AND fk.owner = v_user JOIN all_cons_columns cc ON cc.constraint_name = fk.constraint_name AND cc.owner = fk.owner JOIN all_constraints pk ON pk.constraint_name = fk.r_constraint_name AND pk.owner = v_user JOIN all_cons_columns pc ON pc.constraint_name = pk.constraint_name AND pc.owner = pk.owner WHERE fk.constraint_type = 'R' ) SELECT DISTINCT child_table, parent_table, child_fk_column, parent_pk_column, lvl FROM RECURSIVE_TREE ORDER BY lvl DESC, child_table ) LOOP v_count := v_count + 1; DBMS_OUTPUT.PUT_LINE('Processing table: ' || rec.child_table); LOOP v_sql := 'DELETE FROM ' || v_user || '.' || rec.child_table || ' WHERE ' || rec.child_fk_column || ' IN (SELECT ' || rec.parent_pk_column || ' FROM ' || v_user || '.' || rec.parent_table || ' WHERE DEPTNO = :deptno) AND ROWNUM <= :batch_size'; EXECUTE IMMEDIATE v_sql USING v_deptno, v_batch_size; v_rows := SQL%ROWCOUNT; EXIT WHEN v_rows = 0; DBMS_OUTPUT.PUT_LINE(' Deleted ' || v_rows || ' rows from ' || rec.child_table); COMMIT; END LOOP; END LOOP; -- Delete from root table in batches DBMS_OUTPUT.PUT_LINE('Processing root table: ' || v_root_table); LOOP v_sql := 'DELETE FROM ' || v_user || '.' || v_root_table || ' WHERE DEPTNO = :deptno AND ROWNUM <= :batch_size'; EXECUTE IMMEDIATE v_sql USING v_deptno, v_batch_size; v_rows := SQL%ROWCOUNT; EXIT WHEN v_rows = 0; DBMS_OUTPUT.PUT_LINE(' Deleted ' || v_rows || ' rows from ' || v_root_table); COMMIT; END LOOP; DBMS_OUTPUT.PUT_LINE('--- BATCH FK-SAFE DELETE COMPLETE ---'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLERRM); ROLLBACK; RAISE; END; /***********************SET SERVEROUTPUT ON SIZE 1000000 DECLARE -- Collection types TYPE t_col_list IS TABLE OF VARCHAR2(100); TYPE t_val_list IS TABLE OF VARCHAR2(4000); v_user VARCHAR2(200) := 'SCOTT'; -- Schema v_root_table VARCHAR2(200) := 'DEPT'; -- Root table v_batch_size NUMBER := 1000; -- Root table filters v_filter_cols t_col_list := t_col_list('DEPTNO'); v_filter_vals t_val_list := t_val_list('10'); v_where_clause VARCHAR2(1000); v_script CLOB; v_line VARCHAR2(4000); BEGIN -- Build WHERE clause only for columns that exist v_where_clause := ''; FOR i IN 1..v_filter_cols.COUNT LOOP DECLARE v_cnt NUMBER; BEGIN SELECT COUNT(*) INTO v_cnt FROM all_tab_columns WHERE owner = v_user AND table_name = v_root_table AND column_name = v_filter_cols(i); IF v_cnt > 0 THEN IF v_where_clause IS NOT NULL AND v_where_clause <> '' THEN v_where_clause := v_where_clause || ' AND '; END IF; v_where_clause := v_where_clause || v_filter_cols(i) || ' = ' || CHR(39) || v_filter_vals(i) || CHR(39); END IF; END; END LOOP; DBMS_OUTPUT.PUT_LINE('--- PRINT-ONLY FK-SAFE DELETE SCRIPT ---'); DBMS_OUTPUT.PUT_LINE('Schema: ' || v_user || ' Root: ' || v_root_table); DBMS_OUTPUT.PUT_LINE('Root filter: ' || v_where_clause); DBMS_OUTPUT.PUT_LINE(''); -- Traverse FK tree and print delete statements FOR rec IN ( WITH RECURSIVE_TREE(child_table, parent_table, child_fk_column, parent_pk_column, lvl) AS ( SELECT cc.table_name, p.table_name, cc.column_name, pc.column_name, 1 FROM all_constraints p JOIN all_cons_columns pc ON pc.constraint_name = p.constraint_name AND pc.owner = p.owner JOIN all_constraints fk ON fk.r_constraint_name = p.constraint_name AND fk.owner = p.owner JOIN all_cons_columns cc ON cc.constraint_name = fk.constraint_name AND cc.owner = fk.owner WHERE p.table_name = v_root_table AND p.owner = v_user AND fk.constraint_type = 'R' UNION ALL SELECT cc.table_name, fk_parent.table_name, cc.column_name, pc.column_name, rt.lvl + 1 FROM RECURSIVE_TREE rt JOIN all_constraints fk_parent ON fk_parent.table_name = rt.child_table AND fk_parent.owner = v_user JOIN all_constraints fk ON fk.r_constraint_name = fk_parent.constraint_name AND fk.owner = v_user JOIN all_cons_columns cc ON cc.constraint_name = fk.constraint_name AND cc.owner = fk.owner JOIN all_constraints pk ON pk.constraint_name = fk.r_constraint_name AND pk.owner = v_user JOIN all_cons_columns pc ON pc.constraint_name = pk.constraint_name AND pc.owner = pk.owner WHERE fk.constraint_type = 'R' ) SELECT DISTINCT child_table, parent_table, child_fk_column, parent_pk_column, lvl FROM RECURSIVE_TREE ORDER BY lvl DESC, child_table ) LOOP DBMS_OUTPUT.PUT_LINE('-- Child table: ' || rec.child_table); v_line := 'DELETE FROM ' || v_user || '.' || rec.child_table || CHR(10) || ' WHERE ' || rec.child_fk_column || ' IN (' || CHR(10) || ' SELECT ' || rec.parent_pk_column || CHR(10) || ' FROM ' || v_user || '.' || rec.parent_table || CHR(10) || ' WHERE ' || v_where_clause || CHR(10) || ' );' || CHR(10); DBMS_OUTPUT.PUT_LINE(v_line); END LOOP; -- Root table delete DBMS_OUTPUT.PUT_LINE('-- Root table delete'); v_line := 'DELETE FROM ' || v_user || '.' || v_root_table || CHR(10) || ' WHERE ' || v_where_clause || ';' || CHR(10); DBMS_OUTPUT.PUT_LINE(v_line); DBMS_OUTPUT.PUT_LINE('--- PRINT COMPLETE ---'); END; / --- PRINT-ONLY FK-SAFE DELETE SCRIPT --- Schema: SCOTT Root: DEPT Root filter: DEPTNO = '10' -- Child table: EMP DELETE FROM SCOTT.EMP WHERE DEPTNO IN ( SELECT DEPTNO FROM SCOTT.DEPT WHERE DEPTNO = '10' ); -- Root table delete DELETE FROM SCOTT.DEPT WHERE DEPTNO = '10'; --- PRINT COMPLETE --- PL/SQL procedure successfully completed.
================
https://anuj-singh.blogspot.com/2025/ Delete Foreign Keys and Primary Table Rows
1 comment:
Oracle missing index on foreign key
http://anuj-singh.blogspot.com/2011/09/oracle-missing-index-on-foreign-key.html
Post a Comment