Daily maintenance constraint check issues

The daily maintenance agent job calls spxDailyMaintenanceConstraintCheck which highlights possible data integrity issues that exist in your database.

This identifies issues such as missing foreign keys which may be the case due to legacy data and a lack of historical constraints. Some years ago we instituted an introduction of forced constraints on legacy tables, so if there are issues this data may result in the job surfacing non-failing error messages when this stored procedure is run.

Feel free to run this stored procedure on your database and it will surface any impacted tables.

An example may in the Community table and luOccupation lookup table.

There are Occupation code values listed here linked to codes that no longer exist in luOccupation.

This particular example could be resolved in two main ways:

1. Add a new luOccupation value with missing code
2. Update the Community.OccupationCode value to match one of those in luOccupation

However different approaches may need to be considered for impacted database objects.

If you would like our Professional Services team to review these items and script some fixes for we can do this as chargeable consultative piece of work. Once these issues are resolved a constraint is placed to avoid any similar issue resulting in the future.

Please raise a case if you'd like to discuss further with our team!