Top
Twitter
Photos
Thursday
Jun072012

Adding no-default, non-NULL columns to an existing ActiveRecord model

Recently I had to add a new property to an existing ActiveRecord model. The model represents periodic invoices issued to electricity customers and the new property described the time covered by the invoice in days.

The new property is mandatory, but there is no sensible default that can be filled in for new records. However, for legacy records it was deemed acceptable to set a length of 31 days.

When it comes to writing a migration to add this record, there is a small problem. You can't add a non-NULL column to an existing database table without specifying a default value to use for existing records. I thought about adding the column with a default value, then removing the default later, but initial experimentation showed that you can't use ActiveRecord's change_column to remove a default from a column. So, I ended up writing a migration that added the column without the "not NULL" constraint, populated the existing records, then added the not-NULL constraint.

As you can see, this is fairly inelegant. You could improve it by populating the existing records using an SQL UPDATE statement, but it turns out there is a better way.

Unhappy with the above solution, I looked around a bit more and found that you can remove a column default in ActiveRecord using the change_column_default method. So the way to add a no-default, non-NULL column to an ActiveRecord model is to add the column with a default, then remove the default.

Note that this solution only works if there is a single satisifactory default that can be applied to legacy records. If that is not the case, then you should use the inelegant solution above and replace the logic that fills in the value for existing records.

PrintView Printer Friendly Version

EmailEmail Article to Friend

References (6)

References allow you to track sources for this article, as well as articles that were written in response to this article.
  • Response
    Response: grand seiko 中古
    seiko spirit
  • Response
    Football is seriously 1 of the biggest sports in America. It has a major following.
  • Response
    Response: xovilichter
    the inner curmudgeon - Blog - Adding no-default, non-NULL columns to an existing ActiveRecord model
  • Response
    Recently I had to add a new property to an existing ActiveRecord model. The model represents periodic invoices issued to electricity customers
  • Response
    A checking register is also called a journal account or ledger also. we can keep the deposit and withdraw data in it. well it also helps to remember you that how much money you have in your account at a time
  • Response
    Here we provide you many expence reporting template where you find a quick solution of creating an expence report template.

Reader Comments

There are no comments for this journal entry. To create a new comment, use the form below.

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>
« Split a Ruby Enumerable into chunks | Main | On Future-Proofing »