Trying to grok Rails' use of foreign keys

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
http://api.rubyonrails.org/classes/ActiveRecord/Migration.html

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

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

> 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

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