--- title: "Exercise 9: Create a Referential Integrity Check Template" slug: "exercise-9-create-a-referential-integrity-check-template" description: "Training exercise to learn how to set up a test for referential integrity." updated: 2024-06-21T03:44:53Z published: 2024-06-21T03:44:53Z canonical: "docs.validatar.com/exercise-9-create-a-referential-integrity-check-template" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.validatar.com/llms.txt > Use this file to discover all available pages before exploring further. # Exercise 9: Create a Referential Integrity Check Template Learning Goals - How do I test the same concept in multiple tables? - How do I check for referential integrity? - How do I configure Missing Key Settings? - How do I import a test from the Marketplace? #### Step 1 - Import "Exercise 9: Modified By in User ID List" from Marketplace - Open the Marketplace in the top banner - Search for Exercise 9 - Click on the "Exercise 9: Modified By in User ID List" Card ![image.png](https://cdn.document360.io/a80bc2a3-87d3-4dc2-9244-a16f33ed193c/Images/Documentation/image%2875%29.png) - Click on the "Get" button - Select "Import Directly" - Choose your project - Make sure your Data Source is Mapped correctly - Select the folder you would like to import the Test into ![your-image-description](https://cdn.document360.io/a80bc2a3-87d3-4dc2-9244-a16f33ed193c/Images/Documentation/image%2876%29.png) - Click "Review Changes" - Confirm that you are adding the new test and then hit Import ![your-image-description](https://cdn.document360.io/a80bc2a3-87d3-4dc2-9244-a16f33ed193c/Images/Documentation/image%2877%29.png) #### Step 2 - Review Imported Test Definition - Open the test "Exercise 9: Modified By in User ID List - Starter" and review it's configuration - Note that it is comparing the distinct list of MODIFIED_BY values in the RAW.PAYER table to the distinct list of USER_ID values in the RAW.USER table. Missing Key Settings With a referential integrity test like this, it is not required for every USER_ID to be in the MODIFIED_BY values. Thus we need to allow for missing keys on the Test Data Set (MODIFIED_BY) side to Pass. ![image.png](https://cdn.document360.io/a80bc2a3-87d3-4dc2-9244-a16f33ed193c/Images/Documentation/image%2878%29.png) #### Step 3 - Convert to Template Test - Scroll to the top of the test and click "Convert to Template Test" - Note how the Test has replaced all references to RAW with {{schema.name}}, PAYER with {{table.name}} and MODIFIED_BY with {{column.name}}. ![your-image-description](https://cdn.document360.io/a80bc2a3-87d3-4dc2-9244-a16f33ed193c/Images/Documentation/image%2879%29.png) - This has also happened on the Control side of the test where we do not want changes to be made. - Replace {{schema.name}} with RAW on this side so we are not updating the schema for the Control ![your-image-description](https://cdn.document360.io/a80bc2a3-87d3-4dc2-9244-a16f33ed193c/Images/Documentation/image%2881%29.png) #### Step 4 - Configure Metadata Selection - Switch to the Metadata Selection Tab - Change to the "Use Filters" option - Add a Filter on the Column Name Field Equals to "MODIFIED_BY" ![image.png](https://cdn.document360.io/a80bc2a3-87d3-4dc2-9244-a16f33ed193c/Images/Documentation/image%2882%29.png) - Preview the objects and make sure that all the relevant MODIFIED_BY columns are included in the list. ![your-image-description](https://cdn.document360.io/a80bc2a3-87d3-4dc2-9244-a16f33ed193c/Images/Documentation/image%2883%29.png) #### Step 5 - Save -> Materialize -> Run - Rename your test to "Exercise 9: Modified By in User ID List" - Save the test - Materialize Child Tests - Run Child Tests ![your-image-description](https://cdn.document360.io/a80bc2a3-87d3-4dc2-9244-a16f33ed193c/Images/Documentation/image%2884%29.png)