Referential Integrity cascade delete

[jeremyasmith]

Hi,

I am not sure if I understand referential integrity in Adobe Campaign Properly

Basically I want to create a new schema "Pledges" which is linked to the Recipient table, it will contain a list of pledges that the recipient has made.

I have created the schema (example below) which is fine but I wanted for when a recipient is deleted for the pledges to be deleted as well through a cascaded delete of some form but not sure how to achieve this.

At the moment I can delete either the recipient record or the pledge record without any effect on the other.

The following is what I have tried so far (database postgresql):

<srcSchema _cs="Pledges (nt)" created="2019-11-20 20:42:57.965Z" createdBy-id="0"

           desc="Pledge" entitySchema="xtk:srcSchema" img="xtk:schema.png" label="Pledges"

           labelSingular="Pledge" lastModified="2019-11-21 12:17:26.933Z" mappingType="sql"

           md5="AD17FA7CD5BA316C7EDAD382B8D4C1B3" modifiedBy-id="0" name="Pledges"

           namespace="nt" xtkschema="xtk:srcSchema">

  <element desc="Pledge" label="Pledges" labelSingular="Pledge" name="Pledges">

    <key internal="true" name="pledge">

      <keyfield xpath="@recipientId"/>

      <keyfield xpath="@pledgeCode"/>

    </key>

    <attribute label="Recipient ID" name="recipientId" type="long"/>

    <attribute label="Pledge Code" name="pledgeCode" type="string"/>

    <attribute label="Consent given" name="consent" type="boolean"/>

    <element integrity="neutral" label="Recipient" name="recipient" revIntegrity="own"

             revLabel="pledges" revLink="pledges" target="nms:recipient" type="link">

      <join xpath-dst="@id" xpath-src="@recipientId"/>

    </element>

  </element>

</srcSchema>

[Jonathon_wodnicki]

Hi,

What you have is correct. How are you deleting recipient rows?

Referential integrity is enforced through the app's ORM, not the db.

Thanks,

-Jon

[jeremyasmith]

Thanks for your reply, I am deleting the recipients by moving them to a 'purge' folder and then selecting the purge action on that folder, in the window it shows deletion of subscriptions which I probably mistakenly thought was doing a cascaded delete. We use the 'purge folder' method as it was recommended as the safest way to delete a recipient.

Jeremy

[Jonathon_wodnicki · Accepted solution]

Hi,

Interesting, seems like this process is supposed to emulate Windows' recycle bin? Have to wonder why they didn't just reuse delivery's soft-delete mechanic instead. What happens if you delete a recipient directly?

FWIW nms:subscription uses the same integrity in its join definition as your schema's:

• <element integrity="neutral" label="Recipient" name="recipient" revIntegrity="own"revLabel="Subscriptions" target="nms:recipient" type="link"/>

Thanks,

-Jon

[jeremyasmith]

Hi, Thanks for your reply, based on the above answers I will do some more testing and let you know what I find

Jeremy

[Tobias_Lohmann]

Hi Jeremy,

was following up on this out of curiosity and I think I know what's happening. I have a similar setup and tested both the "Purge folder ..." process and directly deleting a recipients. I see the same behavior: using purge it doesn't delete the linked custom schema elements, but deleting the recipient through rightclick --> delete it follows the integrity cascades and cleans up as desired. 

The reason for this: when you click "Purge folder ..." it executes the ClearFolder function (https://experienceleague.adobe.com/developer/campaign-api/api/sm-recipient-ClearFolder.html); this internally creates and executes some native SQL to delete nms:subscriptions and nms:subHisto entries (and only linked elements form those two tables) for recipients in that folder before it deletes the recipients. This SQL doesn't know about any integrity settings defined in the xml-schema.

As Jonathan pointed out the integrity settings are executed by the client on the object relationship model, eg. when you click "delete" in a list. 

If you look at the link definitions of the subscriptions and subHisto you can see the same revIntegrity=own definitions on the recipient links.

So as long as your not deleting large amounts of recipients (where native SQL execution might make sense) I would recommend using the rightclick-delete option to make sure all linked elements are deleted as per integrity definition.

I hope that clarifies it

Cheers, Tobias