Recognize Disabled/NoCheck Relationships or Constraints and help with Data Cleanup

Question

It is possible to create Disabled or Unchecked relationships. This often happens when converting structures or uploading data from external systems.

A big source is tables and relationships that were created by the Microsoft Upsizing Wizard or its replacement Microsoft SQL Server Migration Assistant. This is because Microsoft Access allows for the creation and use of relationships that are not checked. This also shows up when users have dropdowns/combo-boxes that allow users to select or create their own values.

This can create a massive headache for data cleanup. It would be great if dbFront would recognize disabled relationships and optionally:

• Enforce those relationships in the UI.
• Provide extra tools to help with data cleanup.

Answer 1

dbFront already provides the tools needed to help with the cleanup of invalid data. This can be massively helpful since it allows the data cleanup process to be delegated to another person who might be in a much better position to decide what is valid for the business or not.

Step 1. Find Contraints with Invalid Data

On the database server you can already create database constraints that don't check existing data. dbFront will treat those contraints in the same way as any other constraints. The following SQL Server query selects all foreign keys that are disabled or untrusted.

select * from sys.foreign_keys where is_disabled = 1 or is_not_trusted = 1;

Step 2. Enable Contraints WITH NOCHECK

You will need to enable any disabled constrains for dbFront to use them.

ALTER TABLE [Family] WITH NOCHECK CHECK CONSTRAINT FK_Family_Individuals;

Notice the WITH NOCHECK CHECK, this specifies that the constraint should check all future changes but NOT check existing data unless it is changed.

WARNING: NOCHECK constraints may affect performance because the query optimizer can't trust the data.

---

Step 3. Helping Users Find Invalid Data

It is easy to create Custom Search buttons that use a SQL expression to select invalid data. The following SQL expression selects all records that have a missing or invalid [IndividualId]. You decide what is invalid in your environment.

{[IndividualID]} is null or 
{[IndividualID]} = 0 or
{[IndividualID]} not in (select {[IndividualID]} from [individuals] where IsValid > 0)

The SQL can be used to look for any issues.

You would also need to create a matching button that returns all data.

Step 4. Marking Rows as Invalid

In addition to a Custom Search, it is also possible to have a validation process mark any invalid rows and then use Custom CSS to make those rows stand out it red. Depending on the complexity, it would be good to have a description field clearly specifying the issues.

Step 5. Data Cleanup

Uses can combine the Custom Search button with other filters to find rows that need to be fixed. Once the appropriate rows are selected then they can use the Mass Update Wizard to update the rows.

You can also setup a stored procedure that allows users to move all child data from one existing parent to a new valid parent. You can make this a simple but multi-step process to avoid errors.

• Add a Invalid flag to the table.
• Add a MoveToParent field that is a FK to itself, so the user can select a valid parent.
• Create a Move Data action button that is only visible if the Invalid flag is set.
• The RunProcedure button should l prompt the user for new Valid parent.
• The user:
  1. Checks the Invalid flag and saves to see the Move Data button,
  2. Clicks the Move Data button,
  3. Selects a Valid parent,
  4. and Clicks Move Data, again to complete the move.

NOTE: Remember to ensure that you have a backup of your data.

---

Step 6. Enable Constraints after Cleanup

Once all of the data has been validated then you should enable your constraints without the NOCHECK.

ALTER TABLE [Family] CHECK CONSTRAINT FK_Family_Individuals;

And now you are done.