Remedy for foreign key error when upgrading an @ptitude Observer database

Summary

When upgrading old databases, the upgrade may fail when adding foreign key constraints because they were broken before being added.

It is not possible to add a foreign key constraint to the database if it is currently broken. When upgrading Observer, these constraints will be added to enhance the integrity and performance of the database.

In some cases, these constraints may be broken by leftover orphaned records. This conflict will lead to an error in the table where the issue occurred when trying to add these keys.

Example error message:

“Table 'TrendMultiple' could not add foreign key. Please contact support with this information.”

Solution

Remove the orphaned data before running the upgrade to eliminate the conflict.

Note: Before running any query or upgrading the database, it is strongly recommended to take a backup. This ensures you can quickly and easily revert any changes if there is a problem or mistake.

Running the following query against the database will remove the most common cases of orphaned data.

delete from MeasurementBinaryRaw where IDMeasurement not in (select IDMeasurement from Measurement)

delete from NodePicture where IDNode not in (select IDNode from Node)

delete from PointCommon where IDNode not in (select IDNode from Node)

delete from PointMasConVibration where IDNode not in (select IDNode from PointCommon)

delete from TrendMomMultiple where IDPoint not in (select IDNode from PointCommon)

delete from TrendMultiple where IDPoint not in (select IDNode from PointCommon)

delete from TrendMultipleArchive where IDPoint not in (select IDNode from PointCommon)

Contacting SKF Technical Support Group

For further assistance please open a support case using the Technical Support group's self-help portal at www.skf.com/cm/tsg. Once your support case is submitted, a technician will contact you to begin working on your issue. For urgent issues we are available at these times by phone:

• Monday through Friday, 5:00 a.m. to 4 p.m. Pacific Time -
  Phone: [phone] within the US or [phone] outside the US.
• Monday through Friday, 8:00 a.m. to 4:00 p.m. Central European Time -
  Phone: [phone].
• Monday through Friday, 7:30 a.m. to 4:30 p.m. India Standard Time -
  Phone: [phone].