Create Through table for SubjectGroup and Subject table for Many to Many relationship

[seg1129]

Issue
There is a potential for database integrity error when deleting subjects. because of the Django upgrade Django will no longer allow cascading delete on many to many fields. So when a subject is deleted, they could also be referenced in the subject group table.

Permanent Fix
There is a many to many relationship between Subject group and Subjects, here is the model. we need to create a through table and specify to cascade delete. How to create Django through tables can be found here

Temporary process to delete subjects
Get the following information

• Ehb subject primary key
• Ehb Protocol subject group primary key
  • NOTE: not the BRP protocol ID
• Get the unique Group name for subject in the protocol

Steps to Delete

1. Delete subject from subject group
   • API call documentation can be found here
2. Add ‘BAD:’ to beginning of unique group name for subject in the protocol
   • Can be done through the eHB UI
3. Delete subject from eHB subject table
   • Can be done through the eHB UI

[stxphcodes]

I tried testing this on my local eHB by deleting a subject and checking the Subject Group table and Group table to see if the subject would be deleted on associated protocol. On my local, the subject is deleted from the Subject Group table, but not from the Group table. In addition, I noticed that the table core_subjectgroup_subjects is created in postgres (due to defining the manytomany field. It is not explicitly created through our models.py). When a subject is deleted from a protocol, the count in core_subjectgroup_subjects reflects the deletion

So when a subject is deleted, they could also be referenced in the subject group table.

Should this be, "when a subject is deleted, they could also be referenced in the group table?"

Either way, I think you are right that the solution lies in changing the model of the Subject Group table (either through a through table, or perhaps changing Group to manytomany or adding some sort of foreign key to it)

[stxphcodes]

I’ve been looking at the Django description of the through table, and I’m not sure that it would apply in our case? My understanding of the through table is that it used to specify which field to use for the manytomany if the table that it is linking to has foreign keys of the same type.

Like in their example, the FK for person and intviter are both Person, so in the Group table, the through field is needed to say that the manytomany is referring to Person person and not Person inviter.

In our case, our manytomany references Subject and in core_subjectgroup_subjects, the FK for subject_id correctly references id in the Subject table (there isn’t ambiguity).

I’m not sure but I feel like the modeling of Group and SubjectGroup_subjects needs to include an extra FK somewhere? And I’m not sure how feasible it is to apply that now (with all the group and subj groups that have already been created). A more feasible but sort of hacker-ish solution could be to write a function that would automate what you’re doing now in the temporary solution process? Not sure though - these are just thoughts (hopefully they’re helpful and can definitely help work on the soln once it's established what should be done!)