Forcing a Rails database column to be not null

When creating models in Ruby on Rails, there are a few things to think about. One of them is which bits of data should be required in order for the model to be worth while.

Today I’m going to show you the different ways you can set something to be required at a database level and introduce you to change_column_null.

Not null constraint

Before we get into the Rails bit, I want to talk about “null” and “not null constraint”.

Null, often written in all caps like NULL is the database equivalent to nil which we see in Ruby code. It means there is nothing there. Not to be confused with 0 or "" which can mean “nothing”, NULL means literally nothing has been set.

Databases use the term “not null constraint” to talk about a constraint added to a column to ensure it can never be NULL. Any update or insert to a column that would make it NULL will fail if NOT NULL is correctly set on the column.

Setting a not null constraint when you create the model

The best time to set a not null constraint is when you’re creating the model. In our example we are going to have a table of cats, a cat will have a name. The name has to be required.

Type and run rails generate model Cat name and we will get a migration with the following code;

create_table :cats do |t|
  t.string :name
  t.timestamps
end

Right now the name column doesn’t have any constraints against it. We need to make a small change;

t.string :name, null: false

Now when you run rails db:migrate you will notice your schema contains;

create_table "cats", force: :cascade do |t|
  t.string "name", null: false
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
end

Setting a not null constraint when adding to a table

Let’s say we want to add age to our model, and again have it as a required field. rails generate migration AddAgeToCats age:integer will make a migration with;

def change
  add_column :cats, :age, :integer
end

Much like our create_table from earlier, amend the migration to include null: false;

add_column :cats, :age, :integer, null: false

Now our schema contains;

create_table "cats", force: :cascade do |t|
  t.string "name", null: false
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.integer "age", null: false
end

Setting a not null constraint when a field already exists

What I’ve described so far is the happy path. We have remembered to set null: false at the point we’ve created the column. Sometimes we don’t know that something is going to be a required field until after we have created it. Sometimes business logic changes. This is where change_column_null comes in.

If we imagine that we didn’t add the null: false to age when we added that value, then to enforce a NOT NULL after the fact, write the following rails generate migration AddNotNullConstraintToCats and edit the resulting file to look like;

def change
  change_column_null :cats, :age, false
end

change_column_null takes four arguments, we’ve used three of them.

• table_name :cats
• column_name :age
• null false

The fourth is default which defaults to nil. Setting this will act as a placeholder for any existing records that have NULL for the column we’re updating.

Allowing a column to be null

It might be the case that an existing field used to be critical but now isn’t. We can use change_column_null to turn it back into allowing null values again.

def change
  change_column_null :cats, :age, true
end

A word of caution, it can often be the case that developers will see a validation or a not null constraint and write code that always assumes a value is set. If you change your system to allow null values in the database, also check that application code won’t break if it receives a nil for those columns.

Dealing with existing data which might contain null

If you run change_column_null to set null to false against a column that currently contains NULL values, the migration will fail. There are two things you can do.

Set a default value

If there is a logical default value, that should be used. We could update our name field like so;

def change
  change_column_null :cats, :name, false, 'Unknown'
end

Any cats who don’t have a name set against them will have their name set to “Unknown”. Only you will know if a default makes sense for your project. And note that this doesn’t change the column’s default, so if later on you where to write Cat.create(name: nil) this would fail, it won’t be set to “Unknown”.

We have a guide to setting a column’s default in Rails.

Manually fix your data

If a default doesn’t make sense, then you need to fix your data before enforcing a null: false constraint. The first step in doing this is to set a validation in Rails so that no record can be saved without the appropriate fields set. For example;

class Cat < ApplicationRecord
  validates_presence_of :name
end

Then you can check if there are still any Cats without a name, when there aren’t, you can run the migration. Here is how you can check;

Cat.where(name: nil).count

Why care about not null constraints in databases

Most of the time when editing data, we’re doing it through the application we’ve made, either with web forms, or though a Rails console. However, we should never assume that our application is the only way data can get added or edited. Some people have direct access to the database.

By including constraints like null: false, we stop data getting into a state that our application would not like.

It also serves as useful documentation. Someone can look at the database schema, and without ever looking at application code can see that a particular column must be important for the system.

Keep your model and database logic in sync

One final point, I’ve been stressing the database side of things. It is important to have appropriate model validations to suit the requirements you’re adding to the database. For example, like we did earlier, to say that name is a required field on the Cat model, our model will look like;

class Cat < ApplicationRecord
  validates_presence_of :name
end

Without a model-level validation and with a database constraint of not null, when you attempt to save a model you will get an error like the following;

NOT NULL constraint failed: cats.name

We have found the database consistency gem to be an excellent tool for keeping models and the tables in sync.

Wrapping up

In this article we covered;

• What NOT NULL is and why it is important
• How to add a not null constraint at the time you create tables and columns
• How to add a not null constraint after the fact, as well as some things to look out for
• How to remove a not null constraint
• The different arguments that change_column_null can take