truncate table with foreign keys I have to truncate a table which gave me the below error.


  ORA-02266: unique/primary keys in table referenced by enabled foreign keys. 


I fixed that by checking the below,

SELECT p.table_name "Parent Table", c.table_name "Child Table", 
    p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint" 
    FROM dba_constraints p 
    JOIN dba_constraints c ON(p.constraint_name=c.r_constraint_name) 
    WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U') 
    AND c.constraint_type = 'R' AND p.table_name = UPPER('TABLE_NAME')
    AND p.owner='SCHEMA_NAME'
/


alter table schema.table_name disable constraint SYS_constraint_name;

truncated the table.

Now I am trying to enable the constraint but getting the below,

enable constraint ora-02298 cannot validate - parent keys not found

So I use the below query to identify the records in child table, 

SELECT DISTINCT column_name FROM schema.table_name2 WHERE column_name NOT IN 
(
SELECT column_name FROM schema.table_name1
)


I am unable to delete the above as they have child tables which are acting as foreign keys. 

Now my question - instead of going through loops deleting and disabling keys. How can I truncate a given table and not disable constraints
Oracle 12c introduced TRUNCATE ... CASCADE:

TRUNCATE TABLE


  CASCADE
  
  If you specify CASCADE, then Oracle Database truncates all child
  tables that reference table with an enabled ON DELETE CASCADE
  referential constraint. This is a recursive operation that will
  truncate all child tables, granchild tables, and so on, using the
  specified options.


Keep in mind you need the constraints set to ON DELETE CASCADE for this to work.

In versions below 12c, yes, you can loop through, or instead of TRUNCATE, you can use DELETE, with ON DELETE CASCADE, which will take a lot longer.