Truncate table with foreign keys

constraintforeign keyoracle

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

Best Answer

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.