Trying to grok Rails' use of foreign keys

rubyonrails-talk
1

Sean Colquhoun wrote:

If anybody could be so kind as to quickly tell me, in the following table declaration (from the pickaxe book):

<snip>

what is "fk_items_product"? I can't find any previous mention of it. Which leads me to believe it's like an id for the foreign key.

That is correct. Basically it defines a constraint at the database level that makes sure the foreign key is valid.

The reason that it has a separate name is that one name describes the data, while another name describes the constraint. Here, 'product_id' is the name of the actual column that contains the foreign key IDs -- the data. And 'fk_items_product' is the name for the whole concept of that database-level constraint, rather than any specific bit of data.

So, in the future, if you decided that you didn't need the database to enforce that constraint for you anymore, you would say something like "get rid of the constraint called 'fk_items_product'". However, the data column 'product_id' would still exist, and there'd be nothing stopping you from continuing to use that data to link a product with a line item.

In fact, one of the conventions of Rails is not to bother with constraints at the database level, and just let Rails handle it all for you. (This is a bit of a bone of contention, as some people like the safety net of database-level constraints. But they're not strictly necessary, and the Rails convention is not to use them.)

If I were you, I would not worry about learning about database-level foreign key constraints, apart from having a rough idea about what they are. You don't need them to use associations in Rails.

So in this case, you could leave out the constraint definition entirely, and as far as Rails is concerned nothing would be different:

create table line_items (   id int not null auto_increment,   product_id int not null,   quantity int not null default 0,   unit_price decimal(10,2) not null,

  primary key (id) );

In fact, the preferred method of defining tables these days is to use migrations. This is a way of specifying your database changes in Ruby, so that they can be run automatically against any database. The above table could be defined in a migration with something like:

create_table :line_items do |t|   t.column :product_id, :integer   t.column :quantity, :integer   t.column :unit_price, :float end

For more about migrations, see

But that sounds redundant, since you're setting up "product_id" to pull its value from the other table already.

But if it is, then which one do we use? Would we use "line_items.product_id", or "line_items.fk_items_product" to get the value we want?

You'd use 'line_items.product_id', since that's where the foreign key data lives. As described above, 'fk_items_product' doesn't really 'contain' the foreign key data; all it is is a 'rule' that keeps an eye on the foreign key column.

Also, would there be any other steps afterwards besides declaring the relationship of the two tables in the model?

Nope, that's it.

Chris

2

Sean Colquhoun wrote:

> create table line_items ( > id int not null auto_increment, > product_id int not null, > quantity int not null default 0, > unit_price decimal(10,2) not null, > > primary key (id) > ); >

I do have one question - in the above example, you said that nothing would be different if I just left out the line, but to be honest, I can't see how Rails would be able to make an association from that table declaration without the use of black magic of some sort.

It is sort of black magic (although the official word is 'convention'). You're right that Rails can't figure out the association just from that table definition.

You still need to have

belongs_to :product

and

has_many :line_items

in your LineItem and Product classes respectively. These declarations set up the association in Rails, using the convention that the foreign key for the 'Product' will be called 'product_id'. Similarly, the foreign key for 'Person' would be called 'person_id', and so on.

Without those declarations, 'product_id' is just another data column as far as Rails is concerned, like you say. But with those declarations, Rails understands it to be the foreign key relating each LineItem to a Product.

Chris

3

> create table line_items ( > id int not null auto_increment, > product_id int not null, > quantity int not null default 0, > unit_price decimal(10,2) not null, > > primary key (id) > ); >

Thanks Chris - that did illuminate quite a bit for me. I will check out migrations today and try to figure out how to do them. I do have one question - in the above example, you said that nothing would be different if I just left out the line, but to be honest, I can't see how Rails would be able to make an association from that table declaration without the use of black magic of some sort.

def make_associations    @sacrifice = black_cat    @invocation = abracadabra.say    pot.stir end

The black magic is in the naming of the field. Rails assumes that a relationship (which is defined via belongs_to, has_many, has_one, etc) is modelled as a foreign key field called <name of model>_id.

Note that foreign keys do *not* have to be defined as constraints in the database. A foreign key constraint merely enforces the referential integrity and helps the database optimise its indexes.

For example:

class User   belongs_to :group end

and

class Group   has_many :users end

mean that AR will look for a group_id field in the users table (the belongs_to) side and use that as the foreign key. No DB constraints necessary.

You can - if you really want to, but you better have a very good reason - use a differently named field for the foreign key:

class User   belongs_to :group, :foreign_key=>'some_obscurely_named_field' end

and

class Group   has_many :users, :foreign_key=>'some_obscurely_named_field' end

This will require a field named "some_obscurely_named_field" in the users table.

Furthermore, you can add conditions to your relationships, for example to filter out inactive users:

class Group   has_many :users, :conditions=>'active = 1' end

This will automatically generate the correct query when retrieving users for a group, which means some_group.users will not contain users where active!=1

There's quite a few other goodies, I leave it to you to find them :wink:

May I also suggest the Agile Web Development With Rails book - that has a good rundown of AR features in the framework section.

Cheers, Max

4

Run script/generate scaffold <model name> and, if necessary, change _form to include the new field. Scaffolds are a very simple way of getting a CRUD user interface up and running quickly, but they are not meant for anything but quick prototype testing. In any production application, almost all of the scaffold code will have been replaced failry quickly.

For all intents and purposes, ignore the fact that scaffolds exist. I know that scaffolds are the thing that most people will first notice about Rails, but they are by far the least important feature of the framework.

Max