thr3ads.net - Rails - [Rails] Removing a default value for a foreign key with not null set [Apr 2006]

If this information is useful, please help other people find it:
Share via:

Jonathan Viney

2006-Apr-27 05:59 UTC

[Rails] Removing a default value for a foreign key with not null set

Hi all,

For various reasons I need to have foreign keys with not null constraints.
Eg:

  create_table :people do |t|
    t.column :name, :string
    t.column :household_id, :integer, :null => false
  end

  create_table :households do |t|
    t.column :name, :string
  end

  add_foreign_key_constraint :people, :household_id, :households, :id

So the people table has a foreign key into households (household_id), and
not null is enabled on the column.

My problem is that I get default values of 0 for household_id when
instantiating new Person object.

  Person.new.household_id   #  == 0

validates_presence_of :household_id then passes because of the 0 value, and
the insert fails because 0 is not a valid foreign key.

A simple fix is to use a before_validation and remove and 0 values for _id
fields, but I''m looking for a cleaner solution:

class Person < ActiveRecord::Base
  belongs_to :household
  validates_presence_of :household_id

  def before_validation
    attributes.each do |key, value|
      self[key] = nil if value == 0 and key.ends_with?(''_id'')
    end
  end
end

Is there a better way?

Cheers, Jonathan.
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060427/9406c26f/attachment.html

Liquid

2006-Apr-27 06:12 UTC

head link

[Rails] Removing a default value for a foreign key with not null set

Hi Jonathan

Put a default value of null (or nil in migrations I suppose) in your DB
setup

Then there is no worries about removing it :)


On 4/27/06, Jonathan Viney <jonathan.viney@gmail.com>
wrote:>
> Hi all,
>
> For various reasons I need to have foreign keys with not null constraints.
> Eg:
>
>   create_table :people do |t|
>     t.column :name, :string
>     t.column :household_id, :integer, :null => false
>   end
>
>   create_table :households do |t|
>     t.column :name, :string
>   end
>
>   add_foreign_key_constraint :people, :household_id, :households, :id
>
> So the people table has a foreign key into households (household_id), and
> not null is enabled on the column.
>
> My problem is that I get default values of 0 for household_id when
> instantiating new Person object.
>
>   Person.new.household_id   #  == 0
>
> validates_presence_of :household_id then passes because of the 0 value,
> and the insert fails because 0 is not a valid foreign key.
>
> A simple fix is to use a before_validation and remove and 0 values for _id
> fields, but I''m looking for a cleaner solution:
>
> class Person < ActiveRecord::Base
>   belongs_to :household
>   validates_presence_of :household_id
>
>   def before_validation
>     attributes.each do |key, value|
>       self[key] = nil if value == 0 and
key.ends_with?(''_id'')
>     end
>   end
> end
>
> Is there a better way?
>
> Cheers, Jonathan.
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060427/5886370d/attachment-0001.html

Jonathan Viney

2006-Apr-27 09:22 UTC

head link

[Rails] Removing a default value for a foreign key with not null set

Unfortunately it''s not that simple. When you''ve specified NOT
NULL in Mysql
, you can''t give the column a default of null.

ERROR 1067 (42000): Invalid default value for ''person_id''

That''s fair enough because you''ve said you don''t want
nulls allowed, and
then asked for a default of null.

Any other suggestions?

-Jonathan.

On 4/27/06, Liquid <has.sox@gmail.com> wrote:>
> Hi Jonathan
>
> Put a default value of null (or nil in migrations I suppose) in your DB
> setup
>
> Then there is no worries about removing it :)
>
>
> On 4/27/06, Jonathan Viney <jonathan.viney@gmail.com> wrote:
>
> > Hi all,
>
> For various reasons I need to have foreign keys with not null constraints.
> Eg:
>
>   create_table :people do |t|
>     t.column :name, :string
>     t.column :household_id, :integer, :null => false
>   end
>
>   create_table :households do |t|
>     t.column :name, :string
>   end
>
>   add_foreign_key_constraint :people, :household_id, :households, :id
>
> So the people table has a foreign key into households (household_id), and
> not null is enabled on the column.
>
> My problem is that I get default values of 0 for household_id when
> instantiating new Person object.
>
>   Person.new.household_id   #  == 0
>
> validates_presence_of :household_id then passes because of the 0 value,
> and the insert fails because 0 is not a valid foreign key.
>
> A simple fix is to use a before_validation and remove and 0 values for _id
> fields, but I''m looking for a cleaner solution:
>
> class Person < ActiveRecord::Base
>   belongs_to :household
>   validates_presence_of :household_id
>
>   def before_validation
>     attributes.each do |key, value|
>       self[key] = nil if value == 0 and
key.ends_with?(''_id'')
>     end
>   end
> end
>
> Is there a better way?
>
> Cheers, Jonathan.
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060427/2dea4698/attachment.html

Liquid

2006-Apr-27 12:50 UTC

head link

[Rails] Removing a default value for a foreign key with not null set

Ok Sorry I missed the first part.. My bad

I think you can use another validation here.

The validates_associated method checks the validity of the associated
model.  Note it should be used after a call to validates_presence_of

The docs can be found at

http://api.rubyonrails.org/classes/ActiveRecord/Validations/ClassMethods.html#M000820

Please see below in the model definition.

On 4/27/06, Jonathan Viney <jonathan.viney@gmail.com>
wrote:>
> Unfortunately it''s not that simple. When you''ve specified
NOT NULL in
> Mysql , you can''t give the column a default of null.
>
> ERROR 1067 (42000): Invalid default value for ''person_id''
>
> That''s fair enough because you''ve said you don''t
want nulls allowed, and
> then asked for a default of null.
>
> Any other suggestions?
>
> -Jonathan.
>
>
> On 4/27/06, Liquid <has.sox@gmail.com> wrote:
> >
> > Hi Jonathan
> >
> > Put a default value of null (or nil in migrations I suppose) in your
DB
> > setup
> >
> > Then there is no worries about removing it :)
> >
> >
> > On 4/27/06, Jonathan Viney < jonathan.viney@gmail.com> wrote:
> >
> > >  Hi all,
> >
> > For various reasons I need to have foreign keys with not null
> > constraints. Eg:
> >
> >   create_table :people do |t|
> >     t.column :name, :string
> >     t.column :household_id, :integer, :null => false
> >   end
> >
> >   create_table :households do |t|
> >     t.column :name, :string
> >   end
> >
> >   add_foreign_key_constraint :people, :household_id, :households, :id
> >
> > So the people table has a foreign key into households (household_id),
> > and not null is enabled on the column.
> >
> > My problem is that I get default values of 0 for household_id when
> > instantiating new Person object.
> >
> >   Person.new.household_id   #  == 0
> >
> > validates_presence_of :household_id then passes because of the 0
value,
> > and the insert fails because 0 is not a valid foreign key.
> >
> > A simple fix is to use a before_validation and remove and 0 values for
> > _id fields, but I''m looking for a cleaner solution:
> >
> > class Person < ActiveRecord::Base
> >   belongs_to :household
> >   validates_presence_of :household_id
> >
>         validates_associated :household_id
       end
>
> > Is there a better way?
> >
> > Cheers, Jonathan.
> >
> > _______________________________________________
> > Rails mailing list
> > Rails@lists.rubyonrails.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> >
> >
> >
> > _______________________________________________
> > Rails mailing list
> > Rails@lists.rubyonrails.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> >
> >
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060427/3be39f12/attachment.html

Reasonably Related Threads

Search for more seemingly similar threads

Rails - Apr 2006 - Removing a default value for a foreign key with not null set

[Rails] Removing a default value for a foreign key with not null set

[Rails] Removing a default value for a foreign key with not null set

[Rails] Removing a default value for a foreign key with not null set

[Rails] Removing a default value for a foreign key with not null set

Reasonably Related Threads