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

  • 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. – ypercubeᵀᴹ Sep 27 '18 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. – Albert Godfrind Sep 28 '18 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. – Albert Godfrind Sep 28 '18 at 9:17
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.

  • 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.) – ypercubeᵀᴹ Sep 27 '18 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.