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
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
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… 
On Oct 6, 10:44 pm, “Pardee, Roy” [email protected] 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
Yes. ALWAYS.
Otherwise, you may need to spend a significant amount of time to
investigate what causes invalid data.
Elias O. 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
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 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
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
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?