A Foreign key is a column in a table that refers to the primary key of another table. The foreign key links these two tables and it acts as a cross-reference between tables.
Referential integrity check identifies all base column data which has not been referenced by the selected column of the referenced table. Here, the reference table and columns are user specified.
If the base table is LINEITEM, base column is L_ORDERKEY, Reference table is ORDERS, and reference column is O_ORDERKEY, then the check is defined as,
(not exists (select ORDERS.O_ORDERKEY from ORDERS where ORDERS.O_ORDERKEY=LINEITEM.L_ORDERKEY))
Here, if the LINEITEM.L_ORDERKEY column value does not match with ORDERS.O_ORDERKEY, then those base table records are considered as invalid.
Steps to create a foreign key rule in uArrow:
1. Click + Add button in the foreign key rule for column L_ORDERKEY.
2. You can define foreign key details using below parameters.
| Parameter Name | Description |
|---|---|
| Rule Description | Specify the rule description. |
| Rule Category | Choose or create new dimension to classify rule if required. |
| Reference Table | Choose reference table using select box. |
| Reference Column | Choose relevant column as reference column from select box. |
Click Apply button to update foreign key rule changes, and then click Save button to save the job.