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:

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

Other references:

• Ticket #14732
• HackMD notes
• Backups of deleted orphaned records: JSON files at s3://dsva-appeals-caseflow-prod/orphaned_db_records/ directory in S3
• example PR

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

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.integer :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

For Polymorphic associations

Polymorphic associations require two columns (*_id and *_type), so a DB foreign key 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:

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