This content has been marked as final. Show 3 replies
Welcome to the forums!
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 ) ;
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 )