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