Deleting metadata records from Dataverse database

We're planning to do some cleanup in the metadata records in the Dataverse database. Some changes we are doing through API, some manually and some through SQL statements. When testing the results of the SQL script containing UPDATE statements, we got a Solr re-indexing failure for a dataset where the value for authorAffiliation in table datasetfieldvalue was updated to NULL. So, it seems set value=NULL results in issues for the value fields in the datasetfieldvalue table for some datasetfieldtypes.

Instead of UPDATE, I have now created a script using DELETE statements for values we want to remove, and figured out the following:

1. Records in table datasetfieldvalue are children of records in table datasetfield. Therefore, the records in table datasetfieldvalue must be deleted BEFORE the records of table datasetfield are deleted.

2. Records in table datasetfieldcompound must not be deleted, because none of the items that need to be deleted in our cleanup job are the only fields in the compound metadata field. Deleting the datasetfieldcompound records that correspond to the datasetfieldvalues that need to be deleted would result in other records, which we don't want to delete, lacking their corresponding datasetfieldcompound values.

I ran some examples of the type below in our test environment, and there were no error messages:

DELETE FROM datasetfieldvalue WHERE id=102230;
DELETE FROM datasetfield WHERE id=151349;

Could some of you database experts give some advice on this? Apart from the fact that such changes of course won't be reflected in the version control information of these datasets, is this SQL DELETE method safe to apply?

Thanks!

The issue provides SQL examples against datasetfieldvalue and datasetfield, plus the observed Solr re-indexing failure after setting authorAffiliation to NULL. Start by reviewing the database relationships and cleanup statements in a test environment; done means determining whether the DELETE ordering is safe and how datasetfieldcompound records should be handled.