Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Tools for finding missing Keys in Parent table?
Message
From
02/09/2008 11:58:10
Hilmar Zonneveld
Independent Consultant
Cochabamba, Bolivia
 
 
To
02/09/2008 11:50:30
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Environment versions
Visual FoxPro:
VFP 9 SP2
OS:
Vista
Network:
Windows 2008 Server
Database:
MS SQL Server
Miscellaneous
Thread ID:
01344184
Message ID:
01344188
Views:
25
>I am looking for any utilities to automate the process of looking for child records that have missing parent key records.
>
>For instance Job_Line_Item records, that somehow do not have the matching parent key record.
>
>Job_Line_Item.job_num = Job.job_num
>
>Believe it or not, I am ashamed to say that I have found cases where I have Job_Line_Item records that do not have the associated parent record present.
>
>So now I want to check all the Parent-Child schemas that I have, which are many.
>
>All my data is presently is in free tables. I need to clean it up so I can push it up to SQL Server.
>
>So, are there any tools to automate this, or is it just a manual process?
>
>I figure I will want this to be a weekly process, just to make sure I have good data structure in place. That is why I want to have it somewhat automated.
>
>It will also help me discover where this is happening so I can fix my programs to prevent it from happening.

Well, first of all, you should implement relational integrity, to avoid such things in the first place. Of course, it might require re-writing some of your existing code. Specifically, your current code might delete the parent record first and then the child record. Your new code will have to delete child records first (if you choose to "Restrict"), or ONLY delete the child record (in cases where you feel comfortable with "Cascade").

To check for the missing parent records, you can either use a subquery:
select * from Child where KeyField not in (select KeyField from Parent)
or use an outer join:
select child.*;
  from Parent right join Child on Parent.KeyField = Child.KeyField;
  where isnull(Parent.SomeField)
... since the non-found records from the parent will have null values.
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform