SidewalkWebpage icon indicating copy to clipboard operation
SidewalkWebpage copied to clipboard

Some tables in the db are missing foreign key constraints

Open misaugstad opened this issue 1 year ago • 0 comments

Brief description of problem/feature

I was working on a short SQL script to remove PII from a db so that we could send it to others, but I noticed that removing data wasn't cascading to all the tables you'd expect. The reason is that foreign key constraints are missing in a few places. Going to list what I've found here. I'll go through and add the constraints when I can, and then remove the extra calls from my script to remove PII!

  1. user_role table needs FOREIGN KEY(user_id) REFERENCES sidewalk_user(user_id)
  2. user_role table needs FOREIGN KEY(role_id) REFERENCES role(role_id)
  3. user_current_region table needs FOREIGN KEY(user_id) REFERENCES sidewalk_user(user_id)
  4. user_current_region table needs FOREIGN KEY(region_id) REFERENCES region(region_id)
  5. label_point table needs FOREIGN KEY(label_id) REFERENCES label(label_id)
  6. validation_task_interaction table needs FOREIGN KEY(mission_id) REFERENCES mission(mission_id)

I'm sure there are plenty of other places where such constraints are missing. Might be worth it to just spend an hour combing through our tables and getting them cleaned up at some point.

misaugstad avatar Jun 25 '24 00:06 misaugstad