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