Referential Integrity cascade delete

[#1]

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>

[#2 · 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

[#3]

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

[#4]

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

Jeremy

[#5]

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