I have a process model that runs 31 queries against our database to remove a person’s information. This works great for 29 out of 31 tables. The last 2 tables are related 1 contains the persons basic information the other contains 1 or more associations for that person. The last table has a foreign key. My process model runs without any errors but when I check the tables the last 2 tables still contain the data.
What is the best way to remove the data without removing the foreign key constraint since this is required for data integrity?
Here is the output from running the show create table command agains the child table:
CREATE TABLE `mtrperson_primarybrands` ( `mtrprsn_primrybrnds_persnid` int(11) NOT NULL, `primarybrands` varchar(255) DEFAULT NULL, `mtrperson_primarybrands_idx` int(11) NOT NULL, PRIMARY KEY (`mtrprsn_primrybrnds_persnid`,`mtrperson_primarybrands_idx`), KEY `FK9E2AC30FB7856593` (`mtrprsn_primrybrnds_persnid`), CONSTRAINT `FK9E2AC30FB7856593` FOREIGN KEY (`mtrprsn_primrybrnds_persnid`) REFERENCES `mtrperson` (`personid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
Discussion posts and replies are publicly visible
So from what I'm seeing, the mtrperson_primarybrands table references the mtrperson table, right? If that's the case, then I think you need to remove the mtrperson_primarybrands data before you remove the data in the mtrperson table. This likely means that you will need to create two separate nodes in your delete process, since you need to make sure that one activity occurs before the other.
Right now I am executing them in the same node. I will give your suggestion a try. Thank you Peter.
That did not work. The records remain in both tables.
Hello Helmuta,
I was trying to find some solution and came across CASCASE keyword usage along with foreign key. One of the search results says that ..."A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted.".
You can try implementing same
Thank You.
Can you describe your CDT structure? Do you have nested CDT (i.e. mtrperson_primarybrands references mtrperson or vice versa)? Also, how did you insert the data? Did you write to one table, then the other, or did you use a single Write to Data Store Entity to write to both tables?
I will need to do some more research. I did not create the original application, I am just trying to add some new functionality. i will post more info later.
More information. There is only 1 table in use in our production environment. MTR_person is the active table to remove data. Also, I noticed that when I execute the SQL command in the dtabase it give me a popup box asking Do you really want to delete the data.
If you're only using one table in production, can you remove the foreign key constraint? Then you should be able to delete it. If you can't remove the constraint you might be able to update as mentioned in this post by updating the Cascade setting. This only applies though if you are using nested CDTs in Appian.
If you are using nested CDTs and you want to use this method, go to your CDT and change the property of the nesting relationship to use CASCADE=ALL (the option in the CDT designer should say "Updates to a parent value should also update associated child value(s)."
See this page in the documentation for more information: https://docs.appian.com/suite/help/latest/CDT_Relationships.html. If you still have more questions, can you explain the structure of your CDTs or upload the XSD for the CDT definition? That can help us troubleshoot this issue.
Thanks Peter for your suggestions.
Hello