Skip to content

Add missing foreign key and NOT NULL constraints to todos table.

Andreas Brandl requested to merge 32282-add-foreign-keys-to-todos into master

Adds missing foreign key constraints to todos table as suggested by https://gitlab.com/gitlab-org/gitlab-ce/issues/32282.

Corresponding ee change: https://gitlab.com/gitlab-org/gitlab-ee/merge_requests/4383

FKs added:

todos -> users
todos -> users through author_id
todos -> notes

NOT NULL constraint added for todos#author_id

Database Checklist

When adding migrations:

  • Updated db/schema.rb
  • Added a down method so the migration can be reverted
  • Added the output of the migration(s) to the MR body
  • Added tests for the migration in spec/migrations if necessary (e.g. when migrating data)

When adding foreign keys to existing tables:

  • Included a migration to remove orphaned rows in the source table before adding the foreign key
  • Removed any instances of dependent: ... that may no longer be necessary
$ rake db:migrate
== 20180201110056 AddForeignKeysToTodos: migrating ============================
-- transaction_open?()
   -> 0.0000s
-- execute("SET statement_timeout TO 0")
   -> 0.0002s
-- execute("ALTER TABLE todos\nADD CONSTRAINT fk_d94154aa95\nFOREIGN KEY (user_id)\nREFERENCES users (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0039s
-- execute("ALTER TABLE todos VALIDATE CONSTRAINT fk_d94154aa95;")
   -> 0.0016s
-- transaction_open?()
   -> 0.0000s
-- execute("SET statement_timeout TO 0")
   -> 0.0002s
-- execute("ALTER TABLE todos\nADD CONSTRAINT fk_ccf0373936\nFOREIGN KEY (author_id)\nREFERENCES users (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0017s
-- execute("ALTER TABLE todos VALIDATE CONSTRAINT fk_ccf0373936;")
   -> 0.0016s
-- transaction_open?()
   -> 0.0000s
-- execute("SET statement_timeout TO 0")
   -> 0.0002s
-- execute("ALTER TABLE todos\nADD CONSTRAINT fk_91d1f47b13\nFOREIGN KEY (note_id)\nREFERENCES notes (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0016s
-- execute("ALTER TABLE todos VALIDATE CONSTRAINT fk_91d1f47b13;")
   -> 0.0013s
== 20180201110056 AddForeignKeysToTodos: migrated (0.0176s) ===================

== 20180204200836 ChangeAuthorIdToNotNullInTodos: migrating ===================
-- change_column_null(:todos, :author_id, false)
   -> 0.0015s
== 20180204200836 ChangeAuthorIdToNotNullInTodos: migrated (0.0030s) ==========

General Checklist

Edited by Yorick Peterse

Merge request reports