search

ProjectSidewalk / SidewalkWebpage

Project Sidewalk web page
http://projectsidewalk.org
MIT License
80 stars 23 forks source link

Some tables in the db are missing foreign key constraints #3574

Open misaugstad opened 6 days ago

misaugstad commented 6 days ago
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.