Should I create foreign keys on my database?

rubyonrails-talk
1

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

2

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

3

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:

4

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

5

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

6

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

7

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

8

Fred & Rob - thanks for the help! -Gavin

9

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?