Quality Check Task, Adding Tables and Relationships

Let us go to the Tables tab. In the Tables tab we define relations between tables by adding these in the left-hand column.

Primary key tables are added first, and foreign key tables are added as relations to the primary key tables.

Right-click to add the Item table here. Once added, select No_ as the Primary Key column.

Next we will add a related table to the Item table.

The related table we want to add is the Sales Invoice Line table. And furthermore we will need to point out the Foreign Key column of this table. The Foreign Key of the Sales Invoice Line table is also called No_. However, do not assume that you always have a one-to-one name match on foreign keys versus primary keys. If in doubt, look up the relation from your Data Source View.

If we click the Show SQL button we will be able to see the resulting SQL script from what we have done so far.

In plain text, the SQL script would read something like this:

“Check that all item numbers in the Sales Invoice Line table (the foreign keys) exists in the Item table (the primary keys). If an Item number in the Sales Invoice Line table is found to be missing in the Item table, insert it into the Item table.”

If we do something similar for all known table relations, we will in other words obtain full data integrity: All foreign keys will have matching primary keys.