SidewalkWebpage icon indicating copy to clipboard operation
SidewalkWebpage copied to clipboard

Added evolution file to apply foreign key constraints to database

Open Jetpackjules opened this issue 1 year ago • 1 comments

Resolves #3574

The issue was that missing foreign key constraints would allow for certain incorrect insertions or deletions (Like null), and "removing data wasn't cascading to all the tables you'd expect." I've added a DB conf evolution that should manually add all the proper foreign key constraints and fix the issue.

BEFORE:

image

AFTER:

image

Testing instructions

To check if the fix worked, check foreign key constraints in the relevant database with: Run these lines in a separate WSL terminal in the root directory of the project:

docker exec -it projectsidewalk-db bash

psql -U postgres -d sidewalk

SET search_path TO DATABASE_USER_HERE;

SELECT conname AS constraint_name, conrelid::regclass AS table_name, pg_get_constraintdef(oid) AS definition FROM pg_constraint WHERE contype = 'f' AND connamespace = 'sidewalk_walla_walla'::regnamespace;

(The above should output a table listing all foreign key constraints)

Things to check before submitting the PR
  • [X] I've included before/after screenshots above.
  • [X] I've asked for and included translations for any user facing text that was added or modified.
  • [X] I've updated any logging. Clicks, keyboard presses, and other user interactions should be logged. If you're not sure how (or if you need to update the logging), ask Mikey. Then make sure the documentation on this wiki page is up to date for the logs you added/updated.
  • [X] I've tested on mobile (only needed for the validation page).

Jetpackjules avatar Oct 14 '24 21:10 Jetpackjules

Thanks for the preliminary feedback, I've implemented the changes!

Jetpackjules avatar Oct 19 '24 04:10 Jetpackjules