Are there any reasons you might not want to have a foreign_key in an association?

Question

If you have

Parent
 has_many :children

Child

Is there any reason a foreign key on Child (to Parent) and a corresponding belongs_to :parent might not be desirable?

When, if ever, would you not want your Child to be able to access its parent?

Answer 1

Performance. There is a cost when inserting a child record to determine if there is a parent record. You can still access the parent record (assuming you have a parentID column in the child table, just no referential integrity).

Answer 2

It's a trade-off. The usual argument against foreign keys is that the index for a foreign key incurs some performance overhead on insert/update/delete. Just like any index.

But an index also gives great benefit when you search via that column.

SELECT * FROM Child WHERE parent_id = :id

Also don't underestimate the overhead of searching for orphaned children and cleaning up bollixed references, which are the inevitable consequence of omitting foreign key constraints.

-- Typical chore: searching for orphaned child rows.

SELECT c.* FROM Child c LEFT OUTER JOIN Parent p 
  ON (c.parent_id = p.parent_id) 
WHERE p.parent_id IS NULL;

There are also some database designs in which you can't use foreign keys, such as Polymorphic Associations or Entity-Attribute-Value. But these designs are anti-patterns in their own right.