Database uniqueness constraint not stopping duplicate record creation through association

Question

I am attempting to add a uniqueness constraint to my database to stop duplicate entries being added to a join table. However, it does not seem to be working. I do not have a model for the join table, so I am not adding model level validations.

Here is the migration:

class CreateBreedsAndTags < ActiveRecord::Migration[5.1]
  def change
    create_table :breeds do |t|
      t.string :name, unique: true, present: true
      t.timestamps
    end

    create_table :tags do |t|
      t.string :name, unique: true, present: true
      t.timestamps
    end

    create_join_table :breeds, :tags do |t|
      t.integer :breed_id
      t.integer :tag_id
      t.index [:breed_id, :tag_id], unique: true
    end
  end
end

The Breed and Tag models are both very simple, and they use has_and_belongs_to_many because I wanted to test out the association. I could add add -> { distinct } to the association, but I would like to stop duplicates from being created in the first place.

class Breed < ApplicationRecord
  # Some validations and stuff here
  has_and_belongs_to_many :tags
end

If I create a Breed and Tag in the rails console. I can do something like this even though there is a database level unique constraint on the join table:

b = Breed.create(name: 'b')
t = Tag.create(name: 't')
b << t
b << t
b.save!
b.tags # outputs the same tag multiple times

EDIT:

1) It's worth noting that I found this stack overflow which recommends overriting the << in the association. However, this doesn't explain why my unique constraint is failing.

2) I also found this stack overflow which recommends a db level constraint, but that is not working for me.

EDIT2:

Here is some table information from the database:

       table_name        |               index_name                |   column_name   
-------------------------+-----------------------------------------+-----------------
 ar_internal_metadata    | ar_internal_metadata_pkey               | key
 breed_tags              | breed_tags_pkey                         | id
 breeds                  | breeds_pkey                             | id
 breeds_tags             | index_breeds_tags_on_breed_id           | breed_id
 breeds_tags             | index_breeds_tags_on_tag_id             | tag_id

And I ran a \d breeds_tags

  Table "public.breeds_tags"
  Column  |  Type  | Modifiers 
----------+--------+-----------
 breed_id | bigint | not null
 tag_id   | bigint | not null
Indexes:
    "index_breeds_tags_on_breed_id" btree (breed_id)
    "index_breeds_tags_on_tag_id" btree (tag_id)

Answer 1

Each migration should at most create or alter a single table. Each migration should be an atomical and reverseable change to the db. If you create both the tables and a foreign key that references the same in the same migration what would happen if you try to reverse it?

# rails g model tags name:string
class CreateTags < ActiveRecord::Migration[5.1]
  def change
    create_table :tags do |t|
      t.string :name
      t.timestamps
    end
  end
end

# rails g model breeds name:string
class CreateBreeds < ActiveRecord::Migration[5.1]
  def change
    create_table :breeds do |t|
      t.string :name

      t.timestamps
    end
  end
end

# rails g migration create_join_table_breeds_tags breeds tags
class CreateJoinTableBreedsTags < ActiveRecord::Migration[5.1]
  def change
    create_join_table :breeds, :tags do |t|
      t.index [:breed_id, :tag_id], unique: true
    end
  end
end

Also the create_join_table macro creates the foreign key columns. So you don't need to manually add them:

# don't do this.
t.integer :breed_id
t.integer :tag_id

In fact you should almost never use t.integer for associations. Use the references macro instead.

This creates a uniqueness constraint that works as expected:

=> #<ActiveRecord::Associations::CollectionProxy [#<Tag id: 1, name: "bar", created_at: "2017-11-03 23:34:51", updated_at: "2017-11-03 23:34:51">]>
irb(main):005:0> b.tags << t
   (0.2ms)  BEGIN
  SQL (3.8ms)  INSERT INTO "breeds_tags" ("breed_id", "tag_id") VALUES ($1, $2)  [["breed_id", 1], ["tag_id", 1]]
   (0.2ms)  ROLLBACK
ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "index_breeds_tags_on_breed_id_and_tag_id"
DETAIL:  Key (breed_id, tag_id)=(1, 1) already exists.

However if you need the join to be unique you should use has_many through: and create a model as has_and_belongs_to does not provide a way for the app to check uniqueness before the database driver blows up. It would require you to wrap your code in some really dirty rescue statements to catch the ActiveRecord::RecordNotUnique exception.

This is not a good idea since exceptions should not be used for normal flow control.

# rails g model breed_tag breed:belongs_to 

# the table naming for has_many through: is different
class CreateBreedTags < ActiveRecord::Migration[5.1]
  def change
    create_table :breed_tags do |t|
      t.belongs_to :breed, foreign_key: true
      t.belongs_to :tag, foreign_key: true
      t.index [:breed_id, :tag_id], unique: true
      t.timestamps
    end
  end
end

class BreedTag < ApplicationRecord
  belongs_to :breed
  belongs_to :tag
  validates_uniqueness_of :breed_id, scope: :tag_id
end

class Breed < ApplicationRecord
  has_many :breed_tags
  has_many :tags, through: :breed_tags
end

class Tag < ApplicationRecord
  has_many :breed_tags
  has_many :breeds, through: :breed_tags
end