Merge branch 'ab-49789-fks-want-indexes' into 'master'

Documentation + rubocop for checking that foreign key constraints require an index

Closes #49789

See merge request gitlab-org/gitlab-ce!20964

1 files changed, 28 insertions, 0 deletions

diff --git a/doc/development/migration_style_guide.md b/doc/development/migration_style_guide.md
index a211effdfa7..6f31e5b82e5 100644
--- a/doc/development/migration_style_guide.md
+++ b/doc/development/migration_style_guide.md
@@ -182,6 +182,34 @@ class MyMigration < ActiveRecord::Migration
 end
 ```
 
+## Adding foreign-key constraints
+
+When adding a foreign-key constraint to either an existing or new
+column remember to also add a index on the column.
+
+This is _required_ if the foreign-key constraint specifies
+`ON DELETE CASCADE` or `ON DELETE SET NULL` behavior. On a cascading
+delete, the [corresponding record needs to be retrieved using an
+index](https://www.cybertec-postgresql.com/en/postgresql-indexes-and-foreign-keys/)
+(otherwise, we'd need to scan the whole table) for subsequent update or
+deletion.
+
+Here's an example where we add a new column with a foreign key
+constraint. Note it includes `index: true` to create an index for it.
+
+```ruby
+class Migration < ActiveRecord::Migration
+
+  def change
+    add_reference :model, :other_model, index: true, foreign_key: { on_delete: :cascade }
+  end
+end
+```
+
+When adding a foreign-key constraint to an existing column, we
+have to employ `add_concurrent_foreign_key` and `add_concurrent_index`
+instead of `add_reference`.
+
 ## Adding Columns With Default Values
 
 When adding columns with default values you must use the method