Forum: Ruby on Rails Should I create foreign keys on my database?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
A1247f73598357f06388e863c41aa624?d=identicon&s=25 Elias Orozco (elioncho)
on 2008-10-06 23:06
(Received via mailing list)
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
Ef3aa7f7e577ea8cd620462724ddf73b?d=identicon&s=25 Rob Biedenharn (Guest)
on 2008-10-06 23:15
(Received via mailing list)
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
Rob@AgileConsultingLLC.com
D6434aa0b7b350f8c3ed0119d81b2ead?d=identicon&s=25 Roy Pardee (rpardee)
on 2008-10-06 23:45
(Received via mailing list)
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... ;)
81b61875e41eaa58887543635d556fca?d=identicon&s=25 Frederick Cheung (Guest)
on 2008-10-06 23:58
(Received via mailing list)
On Oct 6, 10:44 pm, "Pardee, Roy" <parde...@ghc.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
Ad68180beef0a583cd6c438de0fd4738?d=identicon&s=25 Yaxm Yaxm (yaxm)
on 2008-10-07 07:46
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
F05df05d7e52f4ad93cd0f9027818c75?d=identicon&s=25 gaveeno (Guest)
on 2008-10-07 17:24
(Received via mailing list)
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
81b61875e41eaa58887543635d556fca?d=identicon&s=25 Frederick Cheung (Guest)
on 2008-10-07 17:25
(Received via mailing list)
On 7 Oct 2008, at 15:43, gaveeno wrote:

>
> 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
Ef3aa7f7e577ea8cd620462724ddf73b?d=identicon&s=25 Rob Biedenharn (Guest)
on 2008-10-07 17:26
(Received via mailing list)
On Oct 7, 2008, at 10:43 AM, gaveeno wrote:
> 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
F05df05d7e52f4ad93cd0f9027818c75?d=identicon&s=25 gaveeno (Guest)
on 2008-10-07 19:07
(Received via mailing list)
Fred & Rob - thanks for the help!
-Gavin

On Oct 7, 10:55 am, Rob Biedenharn <R...@AgileConsultingLLC.com>
25d33a294d8b03b0eec187cb1dc4d8d7?d=identicon&s=25 Srdjan Pejic (batasrki)
on 2008-10-07 20:54
(Received via mailing list)
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?
This topic is locked and can not be replied to.