Migration for naming database constraints
The following discussion from !385 (merged) should be addressed:
-
@anton-akhmerov started a discussion: (+1 comment) Adding naming conventions is relevant for migrations, is that correct?
Correct, otherwise MySQL uses the default naming convention for constraints, which depends on the order constraints were added. Also, the convention is not guaranteed to be the same for all MySQL servers. This means we do not have a reliable way to access the constraints in migrations.
Even after the current migration, there are tons of constraints which still have the default naming convention, shown by %_ibfk_%
in the table below. (Apparently alembic only detects renaming unique constraints, not foreign key constraints). Maybe it's better to solve this in a separate MR with its own migration. This migration would drop ALL constraints and recreate them accordingly.
TABLE | COLUMN | CONSTRAINT | REFERENCED_TABLE | REFERENCED_COLUMN |
---|---|---|---|---|
copy | number | uq_copy__exam_id | ||
copy | _exam_id | uq_copy__exam_id | ||
copy | submission_id | copy_ibfk_1 | submission | id |
copy | _exam_id | fk_copy__exam_id_exam | exam | id |
exam | token | uq_exam_token | ||
exam_widget | exam_id | exam_widget_ibfk_1 | exam | id |
exam_widget | id | exam_widget_ibfk_2 | widget | id |
feedback_option | problem_id | feedback_option_ibfk_1 | problem | id |
feedback_option | parent_id | fk_parent_feedback | feedback_option | id |
grader | oauth_id | uq_grader_oauth_id | ||
mc_option | feedback_id | mc_option_ibfk_1 | feedback_option | id |
mc_option | id | mc_option_ibfk_2 | widget | id |
page | copy_id | uq_page_copy_id | ||
page | number | uq_page_copy_id | ||
page | copy_id | page_ibfk_1 | copy | id |
problem | exam_id | problem_ibfk_1 | exam | id |
problem_widget | id | problem_widget_ibfk_1 | widget | id |
problem_widget | problem_id | problem_widget_ibfk_2 | problem | id |
scan | exam_id | scan_ibfk_1 | exam | id |
solution | grader_id | solution_ibfk_1 | grader | id |
solution | problem_id | solution_ibfk_2 | problem | id |
solution | submission_id | solution_ibfk_3 | submission | id |
solution_feedback | feedback_option_id | solution_feedback_ibfk_1 | feedback_option | id |
solution_feedback | solution_id | solution_feedback_ibfk_2 | solution | id |
student | uq_student_email | |||
submission | exam_id | submission_ibfk_1 | exam | id |
submission | student_id | submission_ibfk_2 | student | id |