Could not delete due to association with another object: ProgramStageInstance

[SaadAlmageedy]

Hi

I tried to delete a program from DHIS2 but I couldn’t. It keeps telling me “Could not delete due to association with another object: ProgramStageInstance”. Also, I tried to delete a stage in a tracker program but it tells me the same thing. Can anyone help me with this?

I am using DHIS2 version 2.34.1.

[stratosilva (Manuel Silva)]

Do you have access to your database and the capacity to run queries?

[SaadAlmageedy]

Yes, I have full access to the database.

[stratosilva (Manuel Silva)]

Deleting a program is not so simple because you may have multiple dependencies.
First, you need to remove any data associated to that program. That implies all TEIs, Program Stage Instances and Program instance. But these have probably dependencies with the audit tables. Also, there are links to program indicators, program rules, event reports, tables, charts…

For example, to delete program stage instances linked to that program, you can try the following (but my guess is that you will get new errors due to other associations), just replace programUID with the UID of the program you want to delete (be careful with delete queries

delete from trackedentitydatavalue 
inner join programstageinstance on trackedentitydatavalue.programstageinstanceid = programstageinstance.programstageinstanceid 
inner join programstage on programstage.programstageid = programstageinstance.programstageid 
inner join program on program.programid = programstage.programid 
where program.uid = 'programUID';

delete from trackedentitydatavalueaudit 
inner join programstageinstance on trackedentitydatavalueaudit.programstageinstanceid = programstageinstance.programstageinstanceid 
inner join programstage on programstage.programstageid = programstageinstance.programstageid 
inner join program on program.programid = programstage.programid 
where program.uid = 'programUID';

delete from programstageinstance
inner join programstage on programstage.programstageid = programstageinstance.programstageid 
inner join program on program.programid = programstage.programid 
where program.uid = 'programUID';

[jthomas (Jins Thomas)]

@Wut_Yee this might be of helpful to you for the similar question you asked here

[SaadAlmageedy]

I will try this, I hope it works. Thank you for your replay and your help.

[SaadAlmageedy]

Thank you for your replay. I will check it out.

[lillian1n2 (Lillian N.)]

Hi everyone,

Were you able to find a solution to the same? Running into the same issue…

[Gassim (AL-Gassim Sharaf Addin)]

@lillian1n2 I’m glad you found a related topic post, so my question is if you tried to use the solution suggested by @stratosilva:

@stratosilva

Deleting a program is not so simple because you may have multiple dependencies.
First, you need to remove any data associated to that program. That implies all TEIs, Program Stage Instances and Program instance. But these have probably dependencies with the audit tables. Also, there are links to program indicators, program rules, event reports, tables, charts…

For example, to delete program stage instances linked to that program, you can try the following (but my guess is that you will get new errors due to other associations), just replace programUID with the UID of the program you want to delete (be careful with delete queries

delete from trackedentitydatavalue 
inner join programstageinstance on trackedentitydatavalue.programstageinstanceid = programstageinstance.programstageinstanceid 
inner join programstage on programstage.programstageid = programstageinstance.programstageid 
inner join program on program.programid = programstage.programid 
where program.uid = 'programUID';

delete from trackedentitydatavalueaudit 
inner join programstageinstance on trackedentitydatavalueaudit.programstageinstanceid = programstageinstance.programstageinstanceid 
inner join programstage on programstage.programstageid = programstageinstance.programstageid 
inner join program on program.programid = programstage.programid 
where program.uid = 'programUID';

delete from programstageinstance
inner join programstage on programstage.programstageid = programstageinstance.programstageid 
inner join program on program.programid = programstage.programid 
where program.uid = 'programUID';

@stratosilva

Deleting a program is not so simple because you may have multiple dependencies.
First, you need to remove any data associated to that program. That implies all TEIs, Program Stage Instances and Program instance. But these have probably dependencies with the audit tables. Also, there are links to program indicators, program rules, event reports, tables, charts…

For example, to delete program stage instances linked to that program, you can try the following (but my guess is that you will get new errors due to other associations), just replace programUID with the UID of the program you want to delete (be careful with delete queries

delete from trackedentitydatavalue 
inner join programstageinstance on trackedentitydatavalue.programstageinstanceid = programstageinstance.programstageinstanceid 
inner join programstage on programstage.programstageid = programstageinstance.programstageid 
inner join program on program.programid = programstage.programid 
where program.uid = 'programUID';

delete from trackedentitydatavalueaudit 
inner join programstageinstance on trackedentitydatavalueaudit.programstageinstanceid = programstageinstance.programstageinstanceid 
inner join programstage on programstage.programstageid = programstageinstance.programstageid 
inner join program on program.programid = programstage.programid 
where program.uid = 'programUID';

delete from programstageinstance
inner join programstage on programstage.programstageid = programstageinstance.programstageid 
inner join program on program.programid = programstage.programid 
where program.uid = 'programUID';

[blen]

@stratosilva

delete from trackedentitydatavalue 
inner join programstageinstance on trackedentitydatavalue.programstageinstanceid = programstageinstance.programstageinstanceid 
inner join programstage on programstage.programstageid = programstageinstance.programstageid 
inner join program on program.programid = programstage.programid 
where program.uid = 'programUID';

delete from trackedentitydatavalueaudit 
inner join programstageinstance on trackedentitydatavalueaudit.programstageinstanceid = programstageinstance.programstageinstanceid 
inner join programstage on programstage.programstageid = programstageinstance.programstageid 
inner join program on program.programid = programstage.programid 
where program.uid = 'programUID';

delete from programstageinstance
inner join programstage on programstage.programstageid = programstageinstance.programstageid 
inner join program on program.programid = programstage.programid 
where program.uid = 'programUID';

Hello there, the sql syntax above is not working , after program.uid is properly replaced. It keeps underlining in red under inner. what should I do.

[stratosilva (Manuel Silva)]

Which query fails for you? What is the query you are running and what is the PSQL error you are seeing?