Hello
I am trying to create a control for foreign key integrity constraint between 2 tables.
it is a simple case of referenced table and referencing table. I want to check if the reference value is exiting or not.
What is the simpliest way to realize that opération ?
thanks
+26FeatureJoiner. Any features that cannot be matched because there is no corresponding value in the other table will output to one of the Unjoined Ports. It detects no matches in both directions both From and To the Lookup table. On the reference table Unjoined Port a further DuplicateFilter can be used to determine what Distinct/Unique lookup values are the ones that violate a Foreign Key constraint ie. don't exist in the Lookup table.
If your data is already in a SQL-capable database, the fastest is to use a SQLExecutor with something like:
select *
from parent_table
where parent_table.foreign_key not in (select primary_key from child_table)This will return all entries in "parent_table" where the attribute "foreign_key" does not reference an existing "primary_key" in "child_table".
This is usually much faster than using the FeatureMerger or FeatureJoiner since the database will automatically leverage any existing indexes and FME does not have to copy all the data into memory first.