CREATING FOREIGN KEYS 324

Active Record to the rescue. Part of its ORM magic is that it converts the low-level foreign key relationships in the database into high-level interobject mappings. It handles the three basic cases.

•One row in table A is associated with zero or one rows in table B.

•One row in table A is associated with an arbitrary number of rows in table B.

•An arbitrary number of rows in table A are associated with an arbitrary number of rows in table B.

We have to give Active Record a little help when it comes to intertable relationships. This isn’t really Active Record’s fault—it isn’t possible to deduce from the schema what kind of intertable relationships the developer intended. However, the amount of help we have to supply is minimal.

18.1Creating Foreign Keys

As we discussed earlier, two tables are related when one table contains a foreign key reference to the primary key of another. In the following migrations, the table line_items contains foreign key references to the products and orders tables.

def self.up

create_table :products do |t| t.column :title, :string

# ...

end

create_table :orders do |t| t.column :name, :string

# ...

end

create_table :line_items do |t|

t.column :product_id,	:integer
t.column :order_id,	:integer
t.column :quantity,	:integer,
t.column :unit_price,	:decimal, :precision => 8, :scale => 2
end
end

It’s worth noting that this migration doesn’t define any foreign key constraints. The intertable relationships are set up simply because the developer will populate the columns product_id and order_id with key values from the products and orders tables. You can also choose to establish these constraints in your migrations (and I personally recommend that you do), but the foreign key support in Rails doesn’t need them.

Report erratum

CREATING FOREIGN KEYS 325

Looking at this migration, we can see why it’s hard for Active Record to divine the relationships between tables automatically. The order_id and product_id foreign key references in the line_items table look identical. However, the product_id column is used to associate a line item with exactly one product. The order_id column is used to associate multiple line items with a single order. The line item is part of the order but references the product.

This example also shows the standard Active Record naming convention. The foreign key column should be named after the class of the target table, converted to lowercase, with _id appended. Note that between the pluralization and _id appending conventions, the assumed foreign key name will be consistently different from the name of the referenced table. If you have an Active Record model called Person, it will map to the database table people. A foreign key reference from some other table to the people table will have the column name person_id.

The other type of relationship is where some number of one item is related to some number of another item (such as products belonging to multiple categories and categories containing multiple products). The SQL convention for handling this uses a third table, called a join table. The join table contains a foreign key for each of the tables it’s linking, so each row in the join table represents a linkage between the two other tables. Here’s another migration.

def self.up

create_table :products do |t| t.column :title, :string

# ...

end

create_table :categories do |t| t.column :name, :string

# ...

end

create_table :categories_products, :id => false do |t| t.column :product_id, :integer

t.column :category_id, :integer end

# Indexes are important for performance if join tables grow big add_index :categories_products, [:product_id, :category_id] add_index :categories_products, :category_id

end

Rails assumes that a join table is named after the two tables it joins (with the names in alphabetical order). Rails will automatically find the join table categories_products linking categories and products. If you used some other name, you’ll need to add a declaration so Rails can find it.

Report erratum

SPECIFYING RELATIONSHIPS IN MODELS 326

Note that our join table does not need an id column for a primary key, because the combination of product and category id is unique. We stopped the migration from automatically adding the id column by specifying :id => false. We then created two indices on the join table. The first, composite index actually serves two purposes: it creates an index that can be searched on both foreign key columns, and with most databases it also creates an index that enables fast lookup by the product id. The second index then completes the picture, allowing fast lookup on category id.

18.2Specifying Relationships in Models

Active Record supports three types of relationship between tables: one-to-one, one-to-many, and many-to-many. You indicate these relationships by adding declarations to your models: has_one, has_many, belongs_to, and the wonderfully named has_and_belongs_to_many.

One-to-One Relationships

A one-to-one association (or, more accurately, a one-to-zero-or-one relationship) is implemented using a foreign key in one row in one table to reference at most a single row in another table. A one-to-one relationship might exist between orders and invoices: for each order there’s at most one invoice.

	invoices					orders
	id
						id
	order_id					name
	. . .
						. . .
class Invoice < ActiveRecord::Base				class Order < ActiveRecord::Base
belongs_to :order					has_one :invoice
# . . .				# . . .
end				end

As the example shows, we declare this in Rails by adding a has_one declaration to the Order model and by adding a belongs_to declaration to the Invoice model.

There’s an important rule illustrated here: the model for the table that contains the foreign key always has the belongs_to declaration.

One-to-Many Relationships

A one-to-many association allows you to represent a collection of objects. For example, an order might have any number of associated line items. In the database, all the line item rows for a particular order contain a foreign key column referring to that order.

Report erratum

SPECIFYING RELATIONSHIPS IN MODELS 327

		line_items					orders
		id					id
		order_id					name
		. . .
							. . .
	class LineItem < ActiveRecord::Base				class Order < ActiveRecord::Base
						has_many :line_items
	belongs_to :order
					# . . .
	# . . .
					end
	end

In Active Record, the parent object (the one that logically contains a collection of child objects) uses has_many to declare its relationship to the child table, and the child table uses belongs_to to indicate its parent. In our example, class

LineItem belongs_to :order and the orders table has_many :line_items.

Note that again, because the line item contains the foreign key, it has the belongs_to declaration.

Many-to-Many Relationships

Finally, we might categorize our products. A product can belong to many categories, and each category may contain multiple products. This is an example of a many-to-many relationship. It’s as if each side of the relationship contains a collection of items on the other side.

categories

categories_products

products

id

category_id

id

name

product_id

name

. . .

. . .

class Category< ActiveRecord::Base

class Product< ActiveRecord::Base

has_and_belongs_to_many :products

has_and_belongs_to_many :categories

# . . .

# . . .

end

end

In Rails we express this by adding the has_and_belongs_to_many declaration to both models. From here on in, we’ll abbreviate this declaration to habtm.

Many-to-many associations are symmetrical—both of the joined tables declare their association with each other using habtm.

Within the database, many-to-many associations are implemented using an intermediate join table. This contains foreign key pairs linking the two target tables. Active Record assumes that this join table’s name is the concatenation of the two target table names in alphabetical order. In our example, we joined the table categories to the table products, so Active Record will look for a join table named categories_products.

Report erratum