Rails - Nov 2006 - Active Record, Foreign Key Object = nil and endless Optimistic Locking exception

Hi,

I have two tables:

----------------------------------------

create table project_types (
  lock_version int default 0,
  id serial,
  name varchar(32) not null,
  lookup varchar(5) not null,
  primary key (id)
);

create table projects (
  lock_version int default 0,
  id serial,
  creation_date date,
  reference varchar(50),
  terminated int,
  customer_id int not null,
  project_type_id int not null,
  primary key (id),
  foreign key (customer_id) references customers (id),
  foreign key (project_type_id) references project_types (id)
);

----------------------------------------

class ProjectType < ActiveRecord::Base
  has_many :projects
end

class Project < ActiveRecord::Base
  belongs_to :project_type
end
----------------------------------------

The following code works just fine:

----------------------------------------
p1 = Project.find(1)
p1.project_type = ProjectType.find(1)
p1.save
p1.project_type = ProjectType.find(2)
p1.save
----------------------------------------

When I try saving a project which has a project_type = nil, I get an exception,
which is 100% normal, since this foreign key is "not null" in the
schema. However, I cannot figure out how to correct the object then, and give it
a better foreign key. I keep on receiving an ActiveRecord::StaleObjectError
exception, as this small example shows:

----------------------------------------
begin
  p1 = Project.find(1)
  p1.project_type = nil
  p1.save
rescue Exception => e
  puts e
  puts "-------------------------------------"
  begin
    p1.project_type = ProjectType.find(1)
    p1.save
  rescue Exception => e
    puts e
    puts "-------------------------------------"
  end
end
----------------------------------------




-------------------------------------
RuntimeError: ERROR     C23502  Mnull value in column
"project_type_id" violates
 not-null constraint    FexecMain.c     L1818   RExecConstraints:         UPDATE
 projects
        SET "creation_date" = ''2006-12-12'',
"customer_id" = 1, "reference" = ''re
f 1'', "terminated" = 1, "project_type_id" = NULL,
"lock_version" = 201
        WHERE id = 1
        AND lock_version = 200
-------------------------------------
Attempted to update a stale object
-------------------------------------





If I replace "nil" with ProjectType.new, things work as expected:

----------------------------------------
begin
  p1 = Project.find(1)
  p1.project_type = ProjectType.new
  p1.save
rescue Exception => e
  puts e
  puts "-------------------------------------"
  begin
    p1.project_type = ProjectType.find(1)
    p1.save
  rescue Exception => e
    puts e
    puts "-------------------------------------"
  end
end
----------------------------------------


-------------------------------------
RuntimeError: ERROR     C23502  Mnull value in column "name" violates
not-null c
onstraint       FexecMain.c     L1818   RExecConstraints: INSERT INTO project_ty
pes ("name", "lock_version", "lookup")
VALUES(NULL, 0, NULL)
-------------------------------------


The object gets corrected in the rescue.



I have the feeling that assigning nil to a foreign key is perfectly acceptable,
no?

---------------
Philippe Lang
Attik System