You may ask yourself after learning about functional consistency, why not making all foreign keys functional consist. This may seems like a smart move, but probably will get you a lot larger subset than you would expect. The following example is to explain what would happen if every foreign key would automatically be made functional consistent. This may be different for each data model and you may skip this part if you are not interested.
Let say we have the following example. We have a RELATIONS table with different types of relations, which contains doctors and patients. The APPOINTMENTS table contains all the appointment between doctors and patient.
The appointment table has two foreign keys. One that uses the DOCTOR_ID to reference the REL_ID column in RELATIONS and one that uses the PATIENT_ID that also references the REL_ID column in RELATIONS.
If we would subset these tables we may want a complete picture of the patient. So we want specific patients and all the appointments belonging the patient. In that case, we must specify that the PATIENT_ID → REL_ID foreign key must be functional consist.
The automatic technical consistency check will always make sure that the right doctors are also subsetted to create a consistent subset.
In this case we don't want the other foreign key between DOCTOR_ID → REL_ID to be functional consistent. Because otherwise it would select all the appointments of the doctors, which may select new patients, which may select other doctors until we have the complete data set instead of a subset.
This is the reason why functional consistency can be checked per foreign key.
Checking all foreign keys to be functional consists will not only return a lot more data than expected but also will iterate many times, because each functional iteration can cause new technical iterations, which can cause other function iterations etc.
|1||Dr. John Jones||Doctor|
|4||Dr. Edward Martin||Doctor|