Trying to grok Rails' use of foreign keys

Rails
1

Sorry to bring it here, guys, but I need to start using foreign keys in
my Rails app, and I still don’t quite get how they work. I can’t find
any resources on the net that are at my level. They all seem to assume a
basic understanding of foreign key syntax, which I don’t quite get yet.

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

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,

constraint fk_items_product foreign key (product_id) references
products(id),

primary key (id)
);

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. 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?

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

Totally lost,
sean

2

PS - sorry to double-post, but when you declare a foreign key, shouldn’t
the field in the scaffolding change to a drop-down instead of a text
box? Having it stay a text field seems to be just asking for trouble to
me, but maybe I’m just doing it wrong (most probably).

3

Sean C. wrote:

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

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

4

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)
);

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

5

Sean C. wrote:

PS - sorry to double-post, but when you declare a foreign key, shouldn’t
the field in the scaffolding change to a drop-down instead of a text
box? Having it stay a text field seems to be just asking for trouble to
me, but maybe I’m just doing it wrong (most probably).

You’re right, the appropriate form element for a foreign key field is
usually a drop-down of some kind, rather than a free-entry text-field.
However, the automatic scaffold templates in Rails don’t handle
associations between models or anything remotely sophisticated –
they’re just a very basic template to get you started editing one
model.

So if you want a drop-down (which you probably do), then you’ll have to
write it yourself!

Chris

6

Sean C. wrote:

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

7

Thanks Chris - that did illuminate quite a bit for me. I will check out
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 _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 D. With Rails book - that
has a good rundown of AR features in the framework section.

Cheers,
Max

8

Run script/generate scaffold 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

9

OK - I did it up like you guys recommended - the only problem now is
that I can no longer see or edit the foreign key field. (It’s called
“album_id”) Even when I make a new item (a photo), there is no longer a
field there, which means I can’t set which album a photo belongs to,
which basically renders the whole affair moot.

Is this normal or am I doing something wrong?

10

Thanks guys. You helped a lot. I think I’ve got it under control now.