Testing foreign keys

If you have foreign and primary keys defined in your database Dataedo will automatically import them and display them in a Relationship tab and ER diagrams. However, when you don't have that foreign key constraints you might want to add relationships just in the documentation. You can easily do that by with user-defined relationships.

From version 10.4 Dataedo enables you to run tests on actual data and check referential integrity in your database.

Primary/foreign keys tests

You can run a test for any manual or imported relationship (for supported sources, see below for list) and they are available on the creation/editing of user-defined relationships dialog window in Desktop.

Test itself is optional and can be passed ("OK") or failed ("FAIL"). Test result has no impact on the relationship itself.

How it works

To test the relationship, Dataedo runs a number of test on primary and foreign tables:

Primary table/key

1. Primary table non-empty test - Does primary table have any rows (is it non-empty). If it is empty then it cannot be a correct primary table and test fails.
2. Not null test - Is primary key column(s) not null. If any of the rows is null then it is not a proper primary key and test fails.
3. Unique test - Is primary column(s) unique. If it isn't then it is not a proper primary key and test fails.

Foreign table/key

1. Foreign table non-empty test - Does foreign table have any rows (is it non-empty). If it is then it is impossible to tell if reference is correct and test fails.
2. Referential integrity test - Does every foreign key value (set of values) have a corresponding value in primary key, i.e. is reference correct/integral. If it isn't then test fails.
3. Correct columns test

Stats

Dataedo presents you a number of statistics to help you better understand the data and potential issues with data quality:

Primary table/key

1. Primary table row count - how many rows primary table holds.
2. Null rows count - how many rows are null in primary key column(s). It should be equal to 0.
3. Non-unique rows count - how many rows are non unique for primary key. There should be 0 rows that are non-unique.

Foreign table/key

1. Foreign keys - Number of unique foreign key values (e.g. unique number of customer_id values)
2. Foreign table key rows count - number of rows in foreign table.
3. Matching foreign keys - Number of foreign key values that have a corresponding value in primary key (correct referential integrity). Should be equal to foreign keys.
4. Matching foreign keys row count - Number of foreign key table rows that have a corresponding value in primary key (correct referential integrity). Should be equal to foreign table key rows count.
5. Null foreign rows count - Number of rows in foreign key table that least one key value is null. Should be 0.
6. Orphaned foreign keys - Number of unique foreign key values that do not have corresponding values in primary key. Should be 0.
7. Orphaned foreign key rows count - Number of rows in foreign table that foreign key does not have corresponding values in primary key. Should be 0.

Details

Apart from statistics, Dataedo provides you with details of rows that do not pass tests:

Orphaned rows

Non unique primary keys

Running tests

Test is available in a window of the creation/editing of user-defined relationships in Desktop. You can access it on Columns or Relationships tab for any table, view or any other tabular object.

To run a test you need to click on a button in the bottom left corner named "Test".

Note: Foreign key tester supports one-to-one relationships as well as many-to-many.

When you click it you may be asked for connection details to the database you're testing against. Provide it and click on "Connect"".

After that you'll see either a green ""OK" or a red "FAILED" text on the bottom of the window. If it states "OK" it means the test finished successfully and the relationship you made is valid. You can click on "Save" to save your work. If it's "FAILED" you can modify it if needed.

By clicking on "Details" You can also see a detailed report on the test's results. For the successful test:

For failed it looks like that:

Supported data sources

This feature is available for the following sources.

• SQL Server
• Azure SQL
• Snowflake
• Postgres
• Percona
• Aurora
• Oracle
• MySQL
• MariaDB
• Redshift