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
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 useDELETE
, withON DELETE CASCADE
, which will take a lot longer.