---
title: "Referential Integrity Validation"
canonical: "https://docs.vaultspeed.com/space/VPD/3032481800/Referential%20Integrity%20Validation"
format: markdown
---
**Referential integrity validation is possible only when the table's primary key is not equal to the business key.**

Reference error handling is enabled by setting the table level parameter `REFERENTIAL_INTEGRITY_VALIDATED` to Y. With this parameter enabled each table in the extraction layer will have a child error table with the same structure as its parent. For each FK in the source table, a specific `ERROR_CODE` field will be added. The name of that field consists of an error code prefix and abbreviated link name (eg* ERROR_CODE_ADDR_CIAI*). The prefix can be customized by changing the source level parameters `ERROR_CODE`.

If a key references an object where the primary key is also the business key, then the referential integrity logic is not used and the EARLY_ARRIVING_FACTS parameter should be enabled instead (there is no need to store error records in that case).

#### Error code values

| **Error code** | **Meaning** |
| --- | --- |
| -1 | Force loading of faulty record (if `REFERENTIAL_INTEGRITY_FORCE_LINK_LOAD = N` ) (manual update of the error table) |
| 0 | The referenced key was found and the current record has not been loaded yet. |
| 1 | The referenced key is not known and the current record has not been loaded yet. |
| 2 | known reference and already loaded |
| 3 | unknown reference and already loaded |
| 4 | Don’t try to load this record anymore (error record disappears) (manual update of the error table) |

 

The creation of a LNK record when there is an unknown HUB can be forced by setting the table level parameter `REFERENTIAL_INTEGRITY_FORCE_LINK_LOAD` to Y.

> ℹ️ When loading a Many To Many link with a driving key configuration the driving key can never be unknown therefore no data will be loaded.

> ⚠️ This logic is currently not available for Satellite objects.

# Loading methods

## Initial Load

![image](media://9cb23553-698c-4227-bf5c-8a0bd9474bc3)

HUB/SAT records are always loaded, no check is done on the `ERROR_CODE` value.

The loading of the LNK/LKS and LND/LDS will depend on the value of the parameter `REFERENTIAL_INTEGRITY_FORCE_LINK_LOAD`. If it’s set to Y then the load will be done with the UNKNOWN linked record otherwise the load will not be done.

 

## Incremental Load

![image](media://54bc1a9d-4dbc-44a8-874d-99a033ad6468)

During incremental extraction, the data from the CDC tables is union-ed with the data from the error tables. Then in staging incoming data is validated and checked for missing business keys.

All records that do not reference existing BK’s have the appropriate error_code set. Then all these invalid records are copied to the error tables in the extraction layer to be reloaded in the next cycle.