Validating a unique record
Tags: ruby, rails, database
Published: 2020-07-28 07:05:27

I'm building a stat tracking site for my local basketball league using a Rails API. I started with [a post about creating custom names](https://dev.to/javila35/creating-a-custom-name-for-a-foreign-key-3d8i) (or aliases) in Rails. In this post I'll be sharing my experience creating a customized `uniqueness` validation.

Validations in general are an important piece of keeping a tidy database. Fortunately Rails comes with plenty built in. There's [a very thorough guide](https://guides.rubyonrails.org/active_record_validations.html) on implementing validations.

## My use case
I keep track of the data for a game by the `Player`, instead of storing all that information on the `Game` table. To find the winner I tally up the points scored by each `Player` on the `home_team` and `away_team` mentioned in my [last post](https://dev.to/javila35/creating-a-custom-name-for-a-foreign-key-3d8i).

The validation I am creating will ensure that there is only one record (`GameStat`) per `Player`, per `Game`. This custom validation ensures that a `Player` only has one entry on each `Game`. Without this check user error could cause problems for my front end (i.e. displaying an incorrect winner).

The code below is simple. If you're familiar with ActiveRecord it may be easy to understand.

```ruby
class GameStat < ApplicationRecord
  belongs_to :player
  belongs_to :game
  validates :game_id, uniqueness: { scope: :player_id, message: 'record already exists' }
end
```

On the 4th line I declare that a `Game` should only have one `GameStat` per `Player` using the `scope: ` option. I also provide a custom message to give myself my users a clear idea of why their attempt at posting statistics is failing. *The message is not necessary as there is a provided default.* 

To explain in it a nontechnical way: every time I create a `GameStat` I check to see if one exists with the same ID as the current game and player. If there is, an error is sent to the user.

Explaining it technically *(pretty much the same)*: Before the new `GameStat` record is created, Rails runs a SQL query on the `GameStat` table checking to see if a record of the same value (`game_id`, and `player_id` thanks to the scope addition) already exists. If the record exists, throw an error.

There is one more step mentioned by the [Rails guide](https://guides.rubyonrails.org/active_record_validations.html#uniqueness).

>Should you wish to create a database constraint to prevent possible violations of a uniqueness validation using the :scope option, you must create a unique index on both columns in your database.

I have to add an index to my `player_id` and `game_id` on my `GameStat` table. I don't fully understand indexing yet. So I had to find some help. Thanks to google, I [found an answer on Stack Overflow](https://stackoverflow.com/a/34720645/5425583). 

```ruby 
class AddUniqueIndexForGameStat < ActiveRecord::Migration[6.0]
  def change
    add_index :game_stats, [:player_id, :game_id], unique: true
  end
end
```
___

Just like that I am no longer able to create duplicate records for a game, or player. 

Thanks for reading, please comment any questions or suggestions for future blog posts. If you have any good resources on learning more about index's I'd love to see them!