Record associations and Foreign Keys

Active Record Associations do not technically require foreign keys constraints to be added to a database, however there are great advantages:

• Protects data from accidental deletion
• Enforces referential integrity of rspecs and data in UAT (i.e., more realistic data)
• Enables automatically updating Caseflow Database Schema Documentation, which will then be more consistent with Rails' associations.
• Other DB analysis tools can make use of the foreign keys.

Other references:

• Ticket #14732
• HackMD notes
• Backups of deleted orphaned records are in S3://dsva-appeals-caseflow-prod/orphaned_db_records/

The PR template reminds developers to run make check-fks, which uses the immigrant gem (PR #16333), to identify missing foreign keys. If a foreign key is not desired, add to config/initializers/immigrant.rb and re-run make check-fks to check that the association is not listed as missing.

Remember to add a belongs_to for your foreign key association to your Rails model. This will enable the schema diagrams to be automatically updated.

For Non-polymorphic associations

Using this in a migration:

table1.references :table2, null: false, index: true, foreign_key: true, comment: "references associated record in table2"

will do something like the following automatically:

table1.bigint :table2_id, null: false, comment: "references associated record in table2"
table1.index ["table2_id"]
add_foreign_key "table1", "table2", column: "table2_id"

Benefits:

• creates columns with correct types (e.g., bigint table2_id)
• automatically add indexes and add_foreign_key

See PR #16440 for another way to add_reference that uses different column names.

For Polymorphic associations

Polymorphic associations require two columns (*_id and *_type) and potentially refer to different tables, so a DB foreign key constraint cannot be added.

strong_migrations warns to create the index separately, so set 'index: false' here

t.references :table3, null: true, polymorphic: true, index: false, comment: "polymorphic association to table3"

Create the index separately (note the column ordering):

# Adding index separately as strong_migrations suggests
add_index :table1, [:table3_type, :table3_id], algorithm: :concurrently, name: "index_..._id"

For more details, see example PR.