Finding orphaned records 1

[KerryL]

In creating a database I made a mistake by not linking a couple of related tables. Now there's data in each and when I try and link them (and enforce referential integrity) I get an error message telling me it can't be done because data in the child table is related to records that no longer exist in the parent table.

I need to enforce referential integrity between the two tables so that when customers are deleted from the parent table, the records in the child table are also deleted. But I can't make that change without first cleaning out the orphaned records in the child table.

What's the best way to do this?

(There are 8000+ records in the parent table, 8600 in the child table.)

Thanks,
KerryL

(If more table detail is needed please let me know.)

 

[PHV]

One way:
DELETE FROM childTable WHERE FKparentID Not In (SELECT PKparentID FROM parentTable);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BNPMike]

The other way is to join from the child table to the parent table. Then select records with a null parent key. Then turn that query into a delete, and press the button.

 

[jrbarnett]

I'd start by using a "Find Unmatched" query to look at the data to see what the data is, before even considering removing it.

John

 

[KerryL]

PHV said:
DELETE FROM childTable WHERE FKparentID Not In (SELECT PKparentID FROM parentTable);
++++++++


So if:
child table = tblContactCats
parent table = tblContacts
Linked key field = ContactID

is this the correct statement:

DELETE FROM tblContactCats WHERE ContactID Not In (SELECT ContactID FROM tblContacts)


If so, where do I run it? Do I create a query based on this SQL statement?

Sorry for the confusion, I'm just trying to make sure I don't hose up my data.

 

[KerryL]

Thanks for everyone's input. I just wanted to let you all know that I was able to find the orphaned records. There were two of them in the child table--they both belonged to a record in the parent table that had been deleted.

This is the query I used to find the orphans:

SELECT tblContactCats.ConCatID
FROM tblContactCats
WHERE (((tblContactCats.ContactID) Not In (SELECT ContactID FROM tblContacts)));

After running this query I verified that the records it found had no parent record, so I deleted them. I was then able to link the two tables and enforce referential integrity.

Thanks again to those who offered their help.