Allow columns ending with _id not to have FK if columns when belong to different gitlab_schemas
In this schema spec we require developers to add a column as an exception if its name ends with _id, but doesn't have a FK. This was added long time ago when we had only 1 database. But now with multiple databases main, ci and sec, also with working on Cells, and having different GitLab schemas that forbid FKs between them, due to Organization Isolation, it has become normal to have a column _id without a FK, especially that we can use Loose Foreign Keys
The goal of this issue is to automate this spec, and requiring exceptions only if:
- The two tables (referencing, referenced) belong to the same GitLab Schema
- If the two GitLab Schemas allow FKs between them. See this example
Implementation
It might be a problem to parse the column name, for example user_id, and figuring out that the referenced table should be users. But this can utilize the pluralize method to make a guess about the referenced table. The same applied to partitioned and CI tables. For example, when the column name is pipeline_id, but the table name is p_ci_pipelines.
Another way to solve it by listing all the existing FKs, and figuring out the referenced table based on existing FKs column names.
Documentation
Make sure you update this documentation section as well: https://docs.gitlab.com/development/database/foreign_keys/#naming-foreign-keys
Goal
The goal of this issue is to be able to reduce the list of the exceptions in the spec file, and have only columns that represent real exceptions. For example, missing FK between two gitlab_ci tables represents a real exception.