DEV Community

Cover image for How and when to add foreign key constraints
Kevin Coleman
Kevin Coleman

Posted on

How and when to add foreign key constraints

Many rails projects rely on application validation to ensure data integrity. With rails presence validation, you can require associations exist in order for an object to be saved. This works until it doesn't.

Not having foreign key constraints

If a developer forgets to define a dependency option on a rails validation (e.g. has_many :users, dependent: :nullify), uses #delete instead of #destroy, or even manually deletes it via a query, and the associated rows will point to empty records. This isn't ideal, because now you can't reliably test if an association exist by seeing if the id exists.



# good
puts "company exists!" if user.company_id

# bad - N+1 to load the company
puts "company exists!" if user.company.id


Enter fullscreen mode Exit fullscreen mode

If the company is deleted, but the users are not deleted with the company, you might be accidentally invalidating your models, thus preventing you from saving any attribute changes!



class User < ApplicationRecord
  belongs_to :company, required: true
end
user.company.delete
user.update name: 'kevin' # false - company is missing


Enter fullscreen mode Exit fullscreen mode

wat. I can't save the name if the company isn't there?

Adding foreign key constraints

In Rails 5, adding foreign key constraints was added to have the database protect the integrity of associated data. Once a foreign key constraint is defined, your database will not allow you to remove records that are required by other tables.



add_foreign_key :users, :companies


Enter fullscreen mode Exit fullscreen mode

logo of the yeet_dba gem

A quick way to add foreign key constraints to your entire rails schema is to use the yeet_dba gem. yeet_dba includes rake tasks and generators that scan your entire database searching for columns missing foreign key constraints. If the data is valid, it can add the foreign key constraint speeding up your joins and where queries or if the data is invalid, it will help you resolve the problem.

By adding foreign key constraints to your database you reduce N+1 calls, improve join and where query performance, and prevent unexpected failures with missing associations.

Further reading

Top comments (2)

Collapse
 
dmfay profile image
Dian Fay

Most databases don't automatically index foreign keys! MySQL's InnoDB engine does, but if you're using something else you'll have to evaluate whether to index the key column(s) yourself.

Collapse
 
kevincolemaninc profile image
Kevin Coleman

oo, I did not know that! I will update my post. Thank you!