How to detect orphaned and correct records?

SQL Apprentice Question
I got a Parent table and one that should be a child table of it but found
the relationship missing. The fields to create an maintain it are there but
the relationship itself is missing. Since this is on a production
environment I want to make sure that the records in te table are OK and if
not will have to bring corrections. What query can I use to find the
possible orphaned records in the child table?


Celko Answers

>> I got a Parent [sic] table and one that should be a child [sic] table of it but found the relationship missing. The fields [sic] to create and maintain it are there but the relationship itself is missing. Since this is on a production environment I want to make sure that the records [sic] in the table are OK and if not will have to bring corrections. What query can I use to find the possible orphaned records [sic] in the child [sic] table? <<


The correct terms are "referenced" and "referencing" tables; you are
using pre-RDBMS terms that apply to pointer chains. Rows are not
records; fields are not columns; tables are not files. The reason you
have orphans is that some moron before you missed these basic facts
(i.e. columns can have REFERENCES and other constraints while fields do
not; tables are part of a whole schema while files are unrelated; etc.)

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it. My guess, based on a total lack of any DDL, is:


--orphan killer
DELETE FROM Children
WHERE NOT EXISTS
(SELECT *
FROM Parents AS P
WHERE P.unknown_key = Children.unknown_key);


Now that the data is cleaned, *immediately* add a PK-FK constraint to
the Children.unknown_key column(s). Something like this:


ALTER TABLE Children
ADD CONSTRAINT foobar
FOREIGN KEY (unknown_key)
REFERENCES Parent( unknown_key)
ON UPDATE CASCADE
ON DELETE CASCADE


Mop the floor, but FIX THE LEAK!!