Rails, add_reference with null constraint on existing table

This is #10stips (where you learn to solve coding problems and prevent your mental health with 10 seconds tips).

Good morning engineers!

How do you add a column with null:false, setting a default value to existing records, but making sure that new records are validated against nil (instead to be assigned a default value)?

This sounds tricky and I'm here to save your life.

Ready?

Add reference with constraint null:false

Today I wanted to add a validated reference column (aka, foreign key) to an existing table with data. Easy, peasy...

add_reference :application_users, :company, index: true, foreign_key: true, null: false

Setting null: false on company_id column creates a constraint where records MUST always have a default value.

This is exactly what I wanted. And if table is empty, there are no problems.

But, what about the default value on existing records?

The migration shown above will fail without providing one.

-- add_reference(:application_users, :company, {:null=>false, :foreign_key=>true, :index=>true})
rails aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::NotNullViolation: ERROR:  column "company_id" contains null values

Caused by:
ActiveRecord::NotNullViolation: PG::NotNullViolation: ERROR:  column "company_id" contains null values

Caused by:
PG::NotNullViolation: ERROR:  column "company_id" contains null values

If null is not allowed by the constraint, and no other default value is offered, the database has no other way to decide.

And here comes the interesting part!

Adding a default value, the proper way

If you know for sure that you have a company with id: 1, you could add default: 1 to set all existing records to belong to that company by default.

But, having multiple environments such as development, staging and production, forces you to use the Company model and retrieve a value from it.

So, I decided to do something like this...

default_company_id = Company.first.try(:id) || Company.create(name: 'Default company').id
add_reference :application_users, :company, index: true, foreign_key: true, null: false, default: default_company_id

But I knew it wasn't over, yet!

I don't want new records to be assigned to default: default_company_id. Instead, I want the database to validate the transaction and gracefully fail if no company is provided.

So, I added a line:

default_company_id = Company.first.try(:id) || Company.create(name: 'Default company').id
add_reference :application_users, :company, index: true, foreign_key: true, null: false, default: default_company_id

# set default back to nil
change_column_default :application_users, :company_id, nil

This sets the default value back to nil... and the constraint is still there, so creating an ApplicationUser without a company_id will raise an exception as expected.

Great! We're done!

An important sidenote

In general, using the model classes in migrations is considered to be an anti-pattern.

I'm using Rails 4 here and with newer versions of Rails you could create an anonymous class which inherits from the ApplicationRecord and then explicitly set the table name to groups, like this:

default_company_id = Class.new(ApplicationRecord)
                            .tap { |c| c.table_name = :companies }
                            .find_or_create_by(name: 'Default company')
                            .id

Was it helpful?

Share it with your friends/colleagues/brothers and sisters.

Bye,
M.