Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Add foreign key constraints to database #1442

Closed
williamjallen opened this issue May 24, 2023 · 1 comment
Closed

Add foreign key constraints to database #1442

williamjallen opened this issue May 24, 2023 · 1 comment
Labels
refactor / internal change

Comments

@williamjallen
Copy link
Collaborator

We currently lack any sort of automated referential data integrity validation. This means that stale data may accumulate in tables if entities are not deleted properly. This also means that it is possible for data errors to go unnoticed, resulting in bugs which are difficult to track down.

It would be good to set up foreign key constraints for each of our relations. Doing so is not necessarily straightforward however. We currently have a number of places where data is not deleted appropriately and it is possible for entities to violate a new constraint. In these instances, the most practical option is probably to just delete the row which doesn't meet the constraint.
This was referenced Aug 14, 2023
Merged
Merged
Merged
github-merge-queue bot pushed a commit that referenced this issue Aug 17, 2023
@williamjallen
Add foreign key constraints for all projectids (#1655)
e9272a7 
Our database schema is currently a mess. With no foreign keys defined,
it is easy to accidentally forget to update a relationship field
properly when an entity is created or deleted. Virtually all modern
relational databases support foreign key constraints, and there is no
reason for us to not add them.

In a first step towards #1442, this PR adds foreign key constraints to
all of the table columns which reference a project's ID. This should be
a relatively low risk operation for production systems, but it creates a
bit of a headache when writing tests, because creating any model which
references a project necessarily requires a project model to be created
as well.
github-merge-queue bot pushed a commit that referenced this issue Aug 18, 2023
@williamjallen
Add foreign key constraints for all userids (#1656)
9f71c66 
This PR makes progress towards #1442 by adding foreign key constraints
to every column which references user IDs.
github-merge-queue bot pushed a commit that referenced this issue Aug 30, 2023
@williamjallen
Add foreign key constraints for all buildids (#1657)
8c059e3 
This PR builds upon the work in #1655, and adds foreign key constraints
to all buildid columns. Like #1655, a handful of changes to our test
suite were required to ensure that the constraints were fully met during
the testing process.

This is related to issue #1442.
@williamjallen
Copy link
Collaborator Author

Closing this in favor of #2093.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
refactor / internal change
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@williamjallen