How to change a nullable column to not nullable in a Rails migration

Question

I created a date column in a previous migration and set it to be nullable  Now I want to change it to be not nullable  How do I go about doing this assuming there are null rows in that database  I m ok with setting those columns to Time now if they re currently null

User · Accepted Answer

If you do it in a migration then you could probably do it like this     Make sure no null value exist MyModel where date column  nil  update all date column  Time now     Change the column to not allow null change column  my models   date column   datetime  null  false

User · Answer

Rails 4    def change   change column null  users   admin  false   end

User · Answer

In Rails 4  this is a better  DRYer  solution   change column null  my models   date column  false   To ensure no records exist with NULL values in that column  you can pass a fourth parameter  which is the default value to use for records with NULL values   change column null  my models   date column  false  Time now

User · Answer

Per the Strong Migrations gem  using change column null in production is a bad idea because it blocks reads and writes while all records are checked  The recommended way to handle these migrations  Postgres specific  is to separate this process into two migrations  One to alter the table with the constraint  class SetSomeColumnNotNull  lt  ActiveRecord  Migration 6 0    def change     safety assured do       execute  ALTER TABLE  quot users quot  ADD CONSTRAINT  quot users some column null quot  CHECK   quot some column quot  IS NOT NULL  NOT VALID      end   end end  And a separate migration to validate it  class ValidateSomeColumnNotNull  lt  ActiveRecord  Migration 6 0    def change     safety assured do       execute  ALTER TABLE  quot users quot  VALIDATE CONSTRAINT  quot users some column null quot       end   end end  The above examples are pulled  and slightly altered  from the linked documentation  Apparently for Postgres 12  you can also add NOT NULL to the schema and then drop the constraint after the validation has been run  class ValidateSomeColumnNotNull  lt  ActiveRecord  Migration 6 0    def change     safety assured do       execute  ALTER TABLE  quot users quot  VALIDATE CONSTRAINT  quot users some column null quot       end        in Postgres 12   you can then safely set NOT NULL on the column     change column null  users   some column  false     safety assured do       execute  ALTER TABLE  quot users quot  DROP CONSTRAINT  quot users some column null quot       end   end end  Naturally  this means your schema will not show that the column is NOT NULL for earlier versions of Postgres  so I d also advise setting a model level validation to require the value to be present  though I d suggest the same even for versions of PG that do allow this step   Further  before running these migrations you ll want to update all existing records with a value other than null  and make sure any production code that writes to the table is not writing null for the value s

User · Answer

Create a migration that has a change column statement with a  default   gt  value   change column  my table   my column   integer   default   gt  0   null   gt  false   See  change column  Depending on the database engine you may need to use change column null

User · Answer

Rails 4  other Rails 4 answers have problems    def change   change column null  users   admin  false   lt put a default value here gt        change column  users   admin   string   default   gt      end   Changing a column with NULL values in it to not allow NULL will cause problems   This is exactly the type of code that will work fine in your development setup and then crash when you try to deploy it to your LIVE production   You should first change NULL values to something valid and then disallow NULLs   The 4th value in change column null does exactly that  See documentation for more details   Also  I generally prefer to set a default value for the field so I won t need to specify the field s value every time I create a new object  I included the commented out code to do that as well

User · Answer

You can t use add timestamps and null false if you have existing records  so here is the solution    def change   add timestamps  buttons  null  true     Button find each    b  b update created at  Time zone now  updated at  Time zone now       change column null  buttons   created at  false    change column null  buttons   updated at  false  end

User · Answer

In Rails 4 02  according to the docs there is no method like update all with 2 arguments  Instead one can use this code     Make sure no null value exist MyModel where date column  nil  update all date column  Time now     Change the column to not allow null change column  my models   date column   datetime  null  false

[ruby-on-rails] How to change a nullable column to not nullable in a Rails migration?

Examples related to ruby-on-rails

Examples related to migration