This content has been marked as final. Show 3 replies
Welcome to the forums!1 person found this helpful
I think the best course of action is to find the child records that don't have an associated parent. Once you do that you can then create the parent records (or correct your script) and then enable the constraint.
Here is the basic skeleton for a query to retrieve the orphaned child records:
SELECT child_primary_key_columns FROM child_table WHERE foreign_key_colums NOT IN ( SELECT primary_key_columns FROM parent_table ) ;
To find, What is exactly going on you can take help from $ORACLE_HOME/rdbms/admin/utlexcpt.sql
Also, search for "ALTER TABLE table_name ENABLE CONSTRAINT con_name EXCEPTIONS INTO EXCEPTIONS" in the Oracle documentation.
The solution to this problem is :
- You can find the data of detail table, which are not there in master table ( because of which you are getting this error) ,
- and then either insert those missing master records in master table or delete those extra detail table records,
- then enable the foreign key constraint.
For example say you have dept is your master table having deptno as primary key , and emp is detail table having deptno as foreign key referencing to dept table.
Using Below query, you can find data which do not validate the foreign key on dept no.
SELECT DISTINCT deptno FROM emp WHERE deptno NOT IN ( SELECT deptno FROM dept )