Hi, I''m getting confused, are references and foreign keys the same thing really? http://www.jonathansng.com/ruby-on-rails/foreign-key-migrations-in-rails-20/ Is there a good example on FKs? I''d like to see how it goes in practice, for example how deleting the parent record would delete any existing records depending on it, and also how to avoid that from occurring... Cheers. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Frederick Cheung
2008-Oct-11 10:19 UTC
Re: Are references and Foreign keys the same thing?
On Oct 11, 10:53 am, Pod Caster <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> Hi, > > I''m getting confused, are references and foreign keys the same thing > really? >Sort of. I suspect what you are actually wondering about are foreign key constraints (where the database enforces that each foo_id column does indeed contain the id of a row from the foos table. Rails doesn''t provide anything builtin for doing this, although there are various plugins. The documentation for your database should provide information on how it handles foreign keys Fred> http://www.jonathansng.com/ruby-on-rails/foreign-key-migrations-in-ra... > > Is there a good example on FKs? > I''d like to see how it goes in practice, for example how deleting the > parent record would delete any existing records depending on it, and > also how to avoid that from occurring... > > Cheers. > -- > Posted viahttp://www.ruby-forum.com/.--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Hi, thanks Fred. Yes you are right I''m after FK constraints. And I guess that a way to do that is for example using this in a migration file: execute "alter table user_images add constraint fk_wi_user foreign key (user_id) references users(id)" Regards -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Hi, Yeah now I checked about foreign key constraints for MySql: http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html I think I understand it a bit better now. Using references or something like t.integer :project_id help creating the relations between tables, and help with queries like SELECT * FROM projects WHERE user_id = 22; The foreign key constrains help using those keys to keep DB data consistent for example. As I mentioned I was actually looking for ON DELETE CASCADE so that was missing on my constraint definition above. It should be: execute "alter table tasks add constraint fk_projects foreign key (project_id) references projects(id) ON DELETE CASCADE" ---- Writing it here to put my thought in order... Please correct me if I''m still mixing up things. Regards -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
To make this easier to do, you might consider writing yourself a little
helper, such as
def foreign_key(foreign_table, foreign_column, primary_table,
primary_column = :id)
execute "
alter table #{foreign_table.to_s}
add constraint fk_#{foreign_table.to_s}_#{foreign_column.to_s}
foreign key (#{foreign_column.to_s}) references
#{primary_table.to_s} (#{primary_column.to_s})
"
end
def delete_foreign_key(foreign_table, foreign_column)
execute "alter table #{foreign_table.to_s} drop constraint
fk_#{foreign_table.to_s}_#{foreign_column.to_s}"
end
so you can use it like
foreign_key :addresses, :person_id, :people
delete_foreign_key :addresses, :person_id
Peace.
--
Posted via http://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---