Should I create foreign keys on my database?

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

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... :wink:

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... :wink:

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

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

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

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 :slight_smile:

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

Fred & Rob - thanks for the help!
-Gavin

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?