[Solved] How to find missing Parent; easy way?

[dreamquartz]

Hi All,

I have an interesting, but nasty problem on my hands.
When going over a design of a db, I found that in the Relation Diagram, a relation was not in place.
The relation is between tPerson.PersonID and tEmployee.FKPersonID.
The moment that I wanted to link the two, the db indicated that it was not possible (see capture).
This means that the missing relation needs to be found, to solve this issue.

What I normally do is to copy the tables in question into Calc, and try to find the missing relation, by aligning the related records, and subtract them. When the result is '0', the relation exists.
It is a method that works for a couple of hundred records, but the db in question is more than 8,000 entries.
Is there a quicker way to find a missing relation?

Dream

UPDATE:
Found a simple solution:

Code: Select all

SELECT *
FROM tEmployee
WHERE NOT FKPersonID IN (
	SELECT PersonID
		FROM tPerson
);

[Villeroy]

tEmployee.FKPersonID is not a primary key so it can not serve as a reference.

[dreamquartz]

tEmployee.FKPersonID is not a primary key so it can not serve as a reference.

K....
I am looking for references in tables where there appears to be no Parent.
I am checking the db for inconsistencies, and found that the Relation between tEmloyee and tPerson could not be made.
What I did is selecting all the Foreign Keys in a table and try to match them with Primary Keys from the Parent.
tEmployee.FKPersonID is the Foreign Key to the Primary Key in tPerson.PersonID.

Selecting all info in tEmployee and with the restriction that the FPersonID is not in the range from tPerson.PersonID.

I can be completely mistaken, but I do get the results I am looking for.

Dream

[Villeroy]

References with no parent prevent the creation of a foreign key because the foreign key would be inconsistent.

Code: Select all

SELECT "A".* 
FROM "A" LEFT JOIN "B" ON "A"."FK_BID" = "B"."ID" 
WHERE "B"."ID" IS NULL

The left join select all records from A (matching or not) and the corresponding Bs with Null values in all non-matching Bs.