Adding `:null => false` column to a table containing records

Rails
1

I’m trying to add a :null => false column to an existing table that
contains existing records. I do it in the standard Rails way -
migrations:

add_column :users, :has_unread_messages, :boolean, { :default => false,
:null => false }

I get an error about the NOT NULL constraint being violated on table
users. Appearantly the migration tries to put NULL in the added column
for all existing rows.

One would wish :default => false would solve this problem. However, it
doesn’t have any effect - the migration statement emits the exact same
error with or without :default => false.

Help?

2

Ok, two final clarifications:

  1. The error pops out when trying to perform the migration, i.e. upon
    rake migrate

  2. The exact same migration works if table users has no records.

-Chris

3

In case it helps in any way, the underlying RDBMS is PostgreSQL, latest
stable release.

4

Ok, thanks to bitsweat, the problem is now clear:

In my Rails version 1.1.6, rake migrate tries to set NOT NULL and
only then to apply the default value.

In the latest Rails version, 1.2rc2, rake migrate applies the default
value and only then sets NOT NULL.

So problem is solved on latest Rails.

Thanks bitsweat.

-Chris

5

On 1/11/07, Hendy I. [email protected] wrote:

users. Appearantly the migration tries to put NULL in the added column

  1. if necessary, UPDATE all rows to FALSE
  2. alter the column to go NOT NULL

PostgreSQL in Rails 1.1.6 used to set the not null constraint before the
default.

In Rails 1.2 and later, add_column performs these steps:

  1. alter table add column
  2. unless default is null:
    3. change column default
    4. if not null, update table set column=default
  3. if not null, alter table set not null

This should resolve the original poster’s issue if using PostgreSQL.

jeremy

6

On 1/7/07, Chris [email protected] wrote:

for all existing rows.

One would wish :default => false would solve this problem. However, it
doesn’t have any effect - the migration statement emits the exact same
error with or without :default => false.

In PostgreSQL, even without Rails migrations (pure SQL or use PgAdmin
III), if you try to add a column with NOT NULL and set default to
FALSE, you still get the same error. The workaround is to:

  1. add the column without NOT NULL.
  2. if necessary, UPDATE all rows to FALSE
  3. alter the column to go NOT NULL

–
Hendy I.
Web: http://hendy.gauldong.net
Mobile: +62 856 24889899
Yahoo Messenger: ceefour666
LinkedIn: http://www.linkedin.com/in/ceefour