Fixing Referential Integrity Problems

Generally, to maintain referential integrity, avoid deleting items from a Domain that reports need to use. Sometimes, removing an item or set from a Domain is unavoidable. For example, you included an item erroneously that exposes confidential data to unauthorized users, or the underlying database changes, making the item invalid.

In a typical scenario, an administrator or data analyst creates a Domain and many end-users create Topics and reports based on it. When deleting Domain items in such a situation, tell users beforehand to modify reports to prevent reports from using the item. Inevitably, a user fails to modify a report as requested, and needs to fix the broken report. The server provides a mechanism to simplify fixing the report. The user can replace the deleted item with a placeholder, open the report, and edit it, even after the original item has been removed.

The following procedure assumes a user created a report based on a Domain. The Domain is similar to the one described in Example of Creating a Domain. The Domain exposes the accounts_opportunities table that contains no useful columns and is necessary only to join the accounts and opportunities tables. Unfortunately, some users were confused by the additional items exposed by the Domain, and used the date_modified item in their reports. You want to delete the accounts_opportunities table from Sets and Items, so it is no longer exposed to users.

To fix referential integrity problems:

1. Locate the problematic Domain in the repository, right-click, and choose Edit. The Edit Domain page appears.
2. On the Edit Domain page, click Edit with Domain Designer.
3. In the Domain Designer, click the Display tab, and select the public_accounts_opportunities set in Sets and Items:

Deletion of Items in Use in Reports from a Domain

4. Click Delete Item. The Confirm dialog box appears, indicating that the item is in use and that a potential referential integrity problem exists. The message includes a link, See items that will be deleted, to the Detail dialog box, containing the items you are deleting from the Domain. The Confirm dialog box is shown in the figure “Indication of Referential Integrity Problem”.

Indication of Referential Integrity Problem

5. Click See items to be deleted. For each deleted item, the Detail window lists the item label, the set and items IDs as a path, and the datatype of the item:

        

List of Items to Be Deleted

The date_modified item has the ID date_modified1, is in the accounts_opportunities set, and is of the type java.sql.Timestamp.

6. Close the Detail dialog box and click OK in the Confirm dialog box.
7. Click the Calculated Fields tab.

Because a calculated field is a custom column of a user-selected type, it can serve as a placeholder. The value of the custom column is irrelevant, but it needs to be recognized as a placeholder when it appears in a report.

8. Enter a field name, type, and an expression that returns a constant value of the same type as the deleted item, as shown in the figure “Creation of Calculated Field as Placeholder for a Deleted Item”, and click Save Field.

Creation of Calculated Field as Placeholder for a Deleted Item

The ZZ prefix in Name distinguishes the placeholder from other columns in the Domain. The value in Type must match the datatype of the deleted item, in this case a timestamp. For information about calculated field datatypes, see Datatypes.

9. Click the Display tab.

A placeholder item must be located in placeholder set to mimic the structure of the deleted item, and generally, in exactly the same path of nested sets as the original item. You deleted the accounts_opportunities set, so you need to create a set for the placeholder item.

10. In Sets and Items, click New Set to create the placeholder set.
11. In Resources, expand the list of Constants and select ZZdate_modified:

Calculated Field Set Up as a Placeholder

12. Click to add the ZZdate_modified to the new set.
13. In Sets and Items, select the placeholder set, newSet1.
14. In Properties, click Edit and change these properties:

Property

New Value

 

ID

accounts_opportunities

 

Label

ZZDeletedSet

 

Description

Placeholder for deleted set

 

15. In Sets and Items, select ZZdate_modified.
16. In Properties, click Edit and change these properties:

Property

New Value

 

ID

date_modified1

 

Label

ZZDeletedField

 

Description

Placeholder for deleted item

 

Distinguishing the placeholders from real items by using the ZZDeletedSet and ZZDeletedField labels discourages their use in building new Ad Hoc views. The IDs, although invisible to users, must be identical to those of the deleted item and set, so the server can open and run the view.

You can see the modified properties for the placeholder item in the figure “Modification of ID Properties to Match the Deleted Item”.

Modification of ID Properties to Match the Deleted Item

17. Click OK in the Domain Designer and then Submit on the Edit Domain page.

Alternatively, you can edit the XML design file of a Domain to fix referential integrity problems. Conceptually, the modifications are the same as described in this procedure, but the order is slightly different.

To fix referential integrity problems in the XML design file of a Domain:

1. Instead of deleting the item, first create the constant calculated field. You can do this in the Domain Designer even before exporting the design file. You must be familiar with the design file syntax to create the calculated field with the correct type and expression, as described in Structure of the Design File.
2. Next, locate the item definition and change its resourceID so that it references the newly created calculated field. This removes the reference to the unwanted column, and replaces it with an item that references the placeholder field.
3. Update the other properties of the item and its enclosing set, using the values shown in the figure “Modification of ID Properties to Match the Deleted Item”. For more information, see Representing Sets and Items in XML.
4. Save the design file and upload it, as described in Uploading a Design File to a Domain.

 

Feedback
randomness