Add documentation about adding foreign keysdocument-foreign-keys

[ci skip]

3 files changed, 58 insertions, 0 deletions

diff --git a/changelogs/unreleased/document-foreign-keys.yml b/changelogs/unreleased/document-foreign-keys.yml
new file mode 100644
index 00000000000..faa467e8185
--- /dev/null
+++ b/changelogs/unreleased/document-foreign-keys.yml
@@ -0,0 +1,4 @@
+---
+title: Add documentation about adding foreign keys
+merge_request:
+author:
diff --git a/doc/development/README.md b/doc/development/README.md
index d04380e5b33..63db332b557 100644
--- a/doc/development/README.md
+++ b/doc/development/README.md
@@ -48,6 +48,7 @@
 - [What requires downtime?](what_requires_downtime.md)
 - [Adding database indexes](adding_database_indexes.md)
 - [Post Deployment Migrations](post_deployment_migrations.md)
+- [Foreign Keys & Associations](foreign_keys.md)
 
 ## Compliance
 
diff --git a/doc/development/foreign_keys.md b/doc/development/foreign_keys.md
new file mode 100644
index 00000000000..8746f988ab0
--- /dev/null
+++ b/doc/development/foreign_keys.md
@@ -0,0 +1,53 @@
+# Foreign Keys & Associations
+
+When adding an association to a model you must also add a foreign key. For
+example, say you have the following model:
+
+```ruby
+class User < ActiveRecord::Base
+  has_many :posts
+end
+```
+
+Here you will need to add a foreign key on column `posts.user_id`. This ensures
+that data consistency is enforced on database level. Foreign keys also mean that
+the database can very quickly remove associated data (e.g. when removing a
+user), instead of Rails having to do this.
+
+## Adding Foreign Keys In Migrations
+
+Foreign keys can be added concurrently using `add_concurrent_foreign_key` as
+defined in `Gitlab::Database::MigrationHelpers`. See the [Migration Style
+Guide](migration_style_guide.md) for more information.
+
+Keep in mind that you can only safely add foreign keys to existing tables after
+you have removed any orphaned rows. The method `add_concurrent_foreign_key`
+does not take care of this so you'll need to do so manually.
+
+## Cascading Deletes
+
+Every foreign key must define an `ON DELETE` clause, and in 99% of the cases
+this should be set to `CASCADE`.
+
+## Indexes
+
+When adding a foreign key in PostgreSQL the column is not indexed automatically,
+thus you must also add a concurrent index. Not doing so will result in cascading
+deletes being very slow.
+
+## Dependent Removals
+
+Don't define options such as `dependent: :destroy` or `dependent: :delete` when
+defining an association _unless_ the association rows use non database related
+data that has to be removed. Even then it's almost always better to add some
+custom logic that removes this non database data in bulk, instead of letting
+Rails call `destroy` on every associated object.
+
+In other words, this is bad and should only be added when absolutely necessary
+and when approved by a database specialist:
+
+```ruby
+class User < ActiveRecord::Base
+  has_many :posts, dependent: :destroy
+end
+```