2

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

Improve this question
3
  • I don't think you can. I think the only possible way is: 1.Disable FK constraints. 2. TRUNCATE tables (starting from children, then parents). 3. Enable FK constraints. Commented Sep 27, 2018 at 9:12
  • This is something you can obviously not do. The very purpose of foreign keys is to maintain referential integrity in your data. Imagine you have employees and departments with a FK from employees to departments. What this means is that an employee must always exist in a department. If you delete one or all departments (or truncate the departments table) - or modify the code of a department, you will get the errors you see. You are essentially making employees orphans, and breaking referential integrity. Commented Sep 28, 2018 at 9:14
  • You can of course force your way by removing the FK constraint. But then you will not be able to re-enable it since employees now exist without the parent department. There are even options (CASCADE) that tells the database to automatically delete the child rows: delete the department and fire all employees in that department in one step. Not sure this is what you want. You may want to go back to your database textbooks and reread the chapters on referential integrity. Commented Sep 28, 2018 at 9:17

2 Answers 2

Reset to default
2

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.

Improve this answer
1
  • Would my idea work in version 11 or previous? (1.Disable FK constraints. 2. TRUNCATE tables (starting from children, then parents). 3. Enable FK constraints.) Commented Sep 27, 2018 at 9:38
0

ashanthan, FK exist to protect the data. You should not have truncated the target table until you understood the relationship between the tables. You may also need to truncate and eventually repopulate the related tables. Or you may need to reload the target table then revalidate the FK removing those rows that are no longer valid from the children. It is also possible from an application point of view that truncating the target table was a mistake, which means you have a mess to clean up. If it turns out to be necessary to replace the data check to see if a recent export of the table exists. Good Luck.

Improve this answer