Rails 1.1: not-null constraint violations


#1

Since upgrading to Rails 1.1, I now get these errors:

PGError: ERROR: null value in column “added” violates not-null
constraint
: INSERT INTO authors (“added”, …) VALUES(NULL, …)

The ‘added’ column has a default of current_timestamp, and this used to
work fine with Rails 1.0. Are we supposed to put default values in
models’ before_create or something now?

Joe


#2

Man, this is REALLY bad…mass breakage all over the place. Why doesn’t
Rails automatically use the default values in the database? Grr…

Joe


#3

I just ran into this a few days ago, and it had nothing to do with 1.1.

It appears that even if you have a default value set in the schema,
pgsql will give this error if you explicitly set a column to NULL (as in
your SQL INSERT below) on a non null column constraint.

The default appears to only be used if you do not specify a value in an
insert or update.

What may have changed in 1.1 is a create statement is now explicitly
setting null on a value that has no input value, rather than leaving it
out of the sql statement.

I do not know what the fix is but let me know if you discover any more.
My fix in my case was to add the columns in the form so they got
specified in the update or create.


#4

If you can compare the sql statement generated with 1.0 vs 1.1 and
confirm if that is the difference.


#5

Jim M. wrote:

What may have changed in 1.1 is a create statement is now explicitly
setting null on a value that has no input value, rather than leaving it
out of the sql statement.

That’s what it seems to be doing. And if it is, that’s lame. I wasn’t
explicitly specifying ‘added=NULL’. I put ‘self.added = Time.now() if
(self.added.nil? or self.added.empty?)’ in before_create to get it to
work again. For columns like this, perhaps we’re supposed to use
‘created_at’ - but what about other columns that Rails doesn’t
automatically update and we want to rely on defaults in the database?
Either way, I’ve got a TON of minor code changes to do. Not happy.

Joe


#6

this particular problem with now() default values is result of
http://dev.rubyonrails.org/changeset/3913

maybe ActiveRecord should on INSERT of new record just omit NULL
values? So newly inserted record will be stored in database with all
needed defaults, defined in schema, but they won’t be visible until
that record will be fetched back from database.


#7

I guess perhaps this is the reason:

Remove broken attempts at handling columns with a default of â??now()â?? in
the postgresql adapter. 2257 [Koz]

http://dev.rubyonrails.org/changeset/3913
http://dev.rubyonrails.org/ticket/2257

Joe


#8

Well, I define defaults in DB and just type:
new_record = MyModel.new_default

( see http://dev.rubyonrails.org/ticket/2259/ )


#9

Lugovoi N. wrote:

maybe ActiveRecord should on INSERT of new record just omit NULL
values? So newly inserted record will be stored in database with all
needed defaults, defined in schema, but they won’t be visible until
that record will be fetched back from database.

Yeah…a newly created record would need to be retrieved right after
insertion. I guess this gets into the application vs. integration
database debate (http://www.loudthinking.com/arc/000516.html). It
appears the choices are to either use ‘created_at/on’ fields, or set the
values directly in ruby.

Joe