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
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