rails database constraint for self referential association

Question

I'm building a rails website, which involves a directed friendship relation. I know in model level, it is a self referential association. And there are methods like has_and_belongs_to for that association.

My question is: how can I set up the database level constraints for this relation. I guess the migration would be something like this, which uses foreign keys to guarantee the referential integrity:

class CreateFriendships < ActiveRecord::Migration
  def change
    create_table :friendships do |t|
      t.belongs_to :user, null: false, foreign_key: true
      t.belongs_to :user, null: false, foreign_key: true

      t.integer :accepted, null: false, default: 0
    end
  end

But when I run rake db:migrate, it has error:

PG::DuplicateObject: ERROR:  constraint "fk_friendships_user_id" for relation "friendships" already exists

As a matter of fact, I'm not even sure whether it is necessary for me to set up the database constraint in this case, since I've seen some people's implementation of friendship relation has no database constraint like this:

create_table :friendships do |t|
  t.integer :user_id
  t.integer :friend_id
  t.timestamps
end

According to Rails Guide

The Active Record way claims that intelligence belongs in your models, not in the database. As such, features such as triggers or constraints, which push some of that intelligence back into the database, are not heavily used.

I'm not sure whether in this case, the database constraints are heavily used.

So is it really necessary for me to set up database level constraints (using foreign keys) in this case? Or I just need to realize the constraints in model level? Thanks!!

Answer 1

I think you're getting confused about the role of foreign_keys in your database architecture.

---

ActiveRecord is just a "coating" for SQL.

It's able to form queries etc which allow you to build associated objects, thus the most important thing you can do is associate those objects properly.

The way to do this - in SQL - is to use a foreign_key, which essentially shows the likes of ActiveRecord (and SQL if you use a join query) which data is associated:

Foreign keys are a standard element of relational database structures, which you probably know.

---

The reason why your data structure is failing is due to the fact you've replicated the user_id foreign key in your friendships table.

You'll want to refer to the following: Rails: self join scheme with has_and_belongs_to_many?

This shows you that if you want to create a self referential join table (such as you're doing), you need to use the following:

#app/models/user.rb
class User < ActiveRecord::Base
  has_and_belongs_to_many :friends, 
              class_name: "User", 
              join_table: :friendships, 
              foreign_key: :user_id, 
              association_foreign_key: :friend_user_id
end

#db/migrate/______.rb
class CreateFriendships < ActiveRecord::Migration
  def self.up
    create_table :friendships, id: false do |t|
      t.integer :user_id
      t.integer :friend_user_id
    end

    add_index(:friendships, [:user_id, :friend_user_id], :unique => true)
    add_index(:friendships, [:friend_user_id, :user_id], :unique => true)
  end

  def self.down
      remove_index(:friendships, [:friend_user_id, :user_id])
      remove_index(:friendships, [:user_id, :friend_user_id])
      drop_table :friendships
  end
end

Notice how the references are for user_id and friend_user_id?

These are the two foreign keys you need to make sure your has_and_belongs_to_many is able to associate two objects of the same model.

Answer 2

You have declared user relation twice:

  t.belongs_to :user, null: false, foreign_key: true
  t.belongs_to :user, null: false, foreign_key: true

Seems that it should be like this:

  t.belongs_to :user, null: false, foreign_key: true
  t.belongs_to :friend, null: false, foreign_key: true

To answer your question: how can I set up the database level constraints for this relation? Answer: Just like you already have.

Often developers go the rails way and set these constraints in model, but it's perfectly reasonable to set them up in database.

EDIT: This will let you create a table with friend_id

class CreateFriendships < ActiveRecord::Migration
  def change
    create_table :friendships do |t|
      t.belongs_to :user, null: false, foreign_key: true
      t.integer :friend_id, null: false

      t.integer :accepted, null: false, default: 0
    end

    add_foreign_key :friendships, :users, column: :friend_id
  end
end