Hello, I have a dilemma. Should I create foreign keys on my database or should I let the app models do all the work? Thanks, Elioncho --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
On Oct 6, 2008, at 5:06 PM, elioncho wrote:> Hello, > > I have a dilemma. Should I create foreign keys on my database or > should I let the app models do all the work? > > Thanks, > > ElionchoIf the Rails app is the only thing hitting the database, then let ActiveRecord handle it. If you''re paranoid or there are other apps updating the database, then go for the safety-net. In any case, you certainly want indexes on your *_id columns if there is a has_many that uses it. (I.e., on the bars.foo_id column if Foo has_many :bars) -Rob Rob Biedenharn http://agileconsultingllc.com Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
I''d make that "or there are other apps that *will ever* update the database". Also, if you use SQL to change data (and I''d include stuff like update_all and delete_all calls) you may well appreciate having the protection of FKs. I may fit that "paranoid" description... ;) -----Original Message----- From: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org [mailto:rubyonrails-talk@googlegroups.com] On Behalf Of Rob Biedenharn Sent: Monday, October 06, 2008 2:14 PM To: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org Subject: [Rails] Re: Should I create foreign keys on my database? On Oct 6, 2008, at 5:06 PM, elioncho wrote:> Hello, > > I have a dilemma. Should I create foreign keys on my database or > should I let the app models do all the work? > > Thanks, > > ElionchoIf the Rails app is the only thing hitting the database, then let ActiveRecord handle it. If you''re paranoid or there are other apps updating the database, then go for the safety-net. In any case, you certainly want indexes on your *_id columns if there is a has_many that uses it. (I.e., on the bars.foo_id column if Foo has_many :bars) -Rob Rob Biedenharn http://agileconsultingllc.com Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
On Oct 6, 10:44 pm, "Pardee, Roy" <parde...-go57ItdSaco@public.gmane.org> wrote:> I''d make that "or there are other apps that *will ever* update the database". Also, if you use SQL to change data (and I''d include stuff like update_all and delete_all calls) you may well appreciate having the protection of FKs. > > I may fit that "paranoid" description... ;)I''m well and truly in there. App level constraints are not hard constraints in the sense that a unique index or a foreign key are: they do suffer from race conditions. Fred> > -----Original Message----- > From: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org [mailto:rubyonrails-talk@googlegroups.com] On Behalf Of Rob Biedenharn > Sent: Monday, October 06, 2008 2:14 PM > To: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org > Subject: [Rails] Re: Should I create foreign keys on my database? > > On Oct 6, 2008, at 5:06 PM, elioncho wrote: > > Hello, > > > I have a dilemma. Should I create foreign keys on my database or > > should I let the app models do all the work? > > > Thanks, > > > Elioncho > > If the Rails app is the only thing hitting the database, then let ActiveRecord handle it. > > If you''re paranoid or there are other apps updating the database, then go for the safety-net. > > In any case, you certainly want indexes on your *_id columns if there is a has_many that uses it. (I.e., on the bars.foo_id column if Foo has_many :bars) > > -Rob > > Rob Biedenharn http://agileconsultingllc.com > R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Yes. ALWAYS. Otherwise, you may need to spend a significant amount of time to investigate what causes invalid data. Elias Orozco wrote:> Hello, > > I have a dilemma. Should I create foreign keys on my database or > should I let the app models do all the work? > > Thanks, > > Elioncho-- 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 -~----------~----~----~----~------~----~------~--~---
Rob - you mentioned that you should have indexes on your *_id columns
if they''re used in has_many relationships. When I created my
migrations for tables containing *_id columns, I set these columns up
like in the following example:
create_table :branches do |t|
t.integer :company_id, :null => false, :options => "CONSTRAINT
fk_branch_company REFERENCES companies(id)"
...
How would I add an index to an existing column? And, for future
tables that I''ll be creating, how would I set up a new column with an
index?
Thanks!
Gavin
On Oct 6, 5:13 pm, Rob Biedenharn
<R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org>
wrote:> On Oct 6, 2008, at 5:06 PM, elioncho wrote:
>
> > Hello,
>
> > I have a dilemma. Should I create foreign keys on my database or
> > should I let the app models do all the work?
>
> > Thanks,
>
> > Elioncho
>
> If the Rails app is the only thing hitting the database, then let
> ActiveRecord handle it.
>
> If you''re paranoid or there are other apps updating the database,
then
> go for the safety-net.
>
> In any case, you certainly want indexes on your *_id columns if there
> is a has_many that uses it. (I.e., on the bars.foo_id column if Foo
> has_many :bars)
>
> -Rob
>
> Rob Biedenharn http://agileconsultingllc.com
> R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
On 7 Oct 2008, at 15:43, gaveeno wrote:> > Rob - you mentioned that you should have indexes on your *_id columns > if they''re used in has_many relationships. When I created my > migrations for tables containing *_id columns, I set these columns up > like in the following example: > > create_table :branches do |t| > t.integer :company_id, :null => false, :options => "CONSTRAINT > fk_branch_company REFERENCES companies(id)" > ... > > How would I add an index to an existing column? And, for future > tables that I''ll be creating, how would I set up a new column with an > index? >add_index adds indices. If you''re using mysql than creating a foreign key constrain implicitly creates an index. Fred> Thanks! > Gavin > > > On Oct 6, 5:13 pm, Rob Biedenharn <R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org> wrote: >> On Oct 6, 2008, at 5:06 PM, elioncho wrote: >> >>> Hello, >> >>> I have a dilemma. Should I create foreign keys on my database or >>> should I let the app models do all the work? >> >>> Thanks, >> >>> Elioncho >> >> If the Rails app is the only thing hitting the database, then let >> ActiveRecord handle it. >> >> If you''re paranoid or there are other apps updating the database, >> then >> go for the safety-net. >> >> In any case, you certainly want indexes on your *_id columns if there >> is a has_many that uses it. (I.e., on the bars.foo_id column if Foo >> has_many :bars) >> >> -Rob >> >> Rob Biedenharn http://agileconsultingllc.com >> R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
On Oct 7, 2008, at 10:43 AM, gaveeno wrote:> Rob - you mentioned that you should have indexes on your *_id columns > if they''re used in has_many relationships. When I created my > migrations for tables containing *_id columns, I set these columns up > like in the following example: > > create_table :branches do |t| > t.integer :company_id, :null => false, :options => "CONSTRAINT > fk_branch_company REFERENCES companies(id)" > ... > > How would I add an index to an existing column? And, for future > tables that I''ll be creating, how would I set up a new column with an > index? > > Thanks! > Gavinadd_index :branches, :company_id Add a :name option if you like (or if you use a database adapter that has a bit of trouble in this area :-) some_company.branches causes something like: SELECT * FROM branches WHERE company_id = #{some_company.id} So you want an index on branches.company_id to make this fast. -Rob> On Oct 6, 5:13 pm, Rob Biedenharn <R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org> wrote: >> On Oct 6, 2008, at 5:06 PM, elioncho wrote: >> >>> Hello, >> >>> I have a dilemma. Should I create foreign keys on my database or >>> should I let the app models do all the work? >> >>> Thanks, >> >>> Elioncho >> >> If the Rails app is the only thing hitting the database, then let >> ActiveRecord handle it. >> >> If you''re paranoid or there are other apps updating the database, >> then >> go for the safety-net. >> >> In any case, you certainly want indexes on your *_id columns if there >> is a has_many that uses it. (I.e., on the bars.foo_id column if Foo >> has_many :bars) >> >> -Rob >> >> Rob Biedenharn http://agileconsultingllc.com >> R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Fred & Rob - thanks for the help! -Gavin On Oct 7, 10:55 am, Rob Biedenharn <R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org> wrote:> On Oct 7, 2008, at 10:43 AM, gaveeno wrote: > > > > > Rob - you mentioned that you should have indexes on your *_id columns > > if they''re used in has_many relationships. When I created my > > migrations for tables containing *_id columns, I set these columns up > > like in the following example: > > > create_table :branches do |t| > > t.integer :company_id, :null => false, :options => "CONSTRAINT > > fk_branch_company REFERENCES companies(id)" > > ... > > > How would I add an index to an existing column? And, for future > > tables that I''ll be creating, how would I set up a new column with an > > index? > > > Thanks! > > Gavin > > add_index :branches, :company_id > > Add a :name option if you like (or if you use a database adapter that > has a bit of trouble in this area :-) > > some_company.branches causes something like: > > SELECT * FROM branches WHERE company_id = #{some_company.id} > > So you want an index on branches.company_id to make this fast. > > -Rob > > > On Oct 6, 5:13 pm, Rob Biedenharn <R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org> wrote: > >> On Oct 6, 2008, at 5:06 PM, elioncho wrote: > > >>> Hello, > > >>> I have a dilemma. Should I create foreign keys on my database or > >>> should I let the app models do all the work? > > >>> Thanks, > > >>> Elioncho > > >> If the Rails app is the only thing hitting the database, then let > >> ActiveRecord handle it. > > >> If you''re paranoid or there are other apps updating the database, > >> then > >> go for the safety-net. > > >> In any case, you certainly want indexes on your *_id columns if there > >> is a has_many that uses it. (I.e., on the bars.foo_id column if Foo > >> has_many :bars) > > >> -Rob > > >> Rob Biedenharn http://agileconsultingllc.com > >> R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
This may be a bit off-topic, but I am of the opinion that you need a strong data model to effectively work in Rails. Therefore, anything that makes sense when creating a "normal" data model applies to the Rails one. I mean, you wouldn''t leave off FK constraints if you were writing a Java or a .NET web app, would you? On Oct 7, 1:06 pm, gaveeno <gavin.to...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Fred & Rob - thanks for the help! > -Gavin > > On Oct 7, 10:55 am, Rob Biedenharn <R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org> > wrote: > > > On Oct 7, 2008, at 10:43 AM, gaveeno wrote: > > > > Rob - you mentioned that you should have indexes on your *_id columns > > > if they''re used in has_many relationships. When I created my > > > migrations for tables containing *_id columns, I set these columns up > > > like in the following example: > > > > create_table :branches do |t| > > > t.integer :company_id, :null => false, :options => "CONSTRAINT > > > fk_branch_company REFERENCES companies(id)" > > > ... > > > > How would I add an index to an existing column? And, for future > > > tables that I''ll be creating, how would I set up a new column with an > > > index? > > > > Thanks! > > > Gavin > > > add_index :branches, :company_id > > > Add a :name option if you like (or if you use a database adapter that > > has a bit of trouble in this area :-) > > > some_company.branches causes something like: > > > SELECT * FROM branches WHERE company_id = #{some_company.id} > > > So you want an index on branches.company_id to make this fast. > > > -Rob > > > > On Oct 6, 5:13 pm, Rob Biedenharn <R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org> wrote: > > >> On Oct 6, 2008, at 5:06 PM, elioncho wrote: > > > >>> Hello, > > > >>> I have a dilemma. Should I create foreign keys on my database or > > >>> should I let the app models do all the work? > > > >>> Thanks, > > > >>> Elioncho > > > >> If the Rails app is the only thing hitting the database, then let > > >> ActiveRecord handle it. > > > >> If you''re paranoid or there are other apps updating the database, > > >> then > > >> go for the safety-net. > > > >> In any case, you certainly want indexes on your *_id columns if there > > >> is a has_many that uses it. (I.e., on the bars.foo_id column if Foo > > >> has_many :bars) > > > >> -Rob > > > >> Rob Biedenharn http://agileconsultingllc.com > > >> R...-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---