Enforcing Unique Constraints Across Multiple Columns in Ruby on Rails
Tags: rails, ruby, programming, activerecord
Published: 2023-01-05 03:41:34

Today, we are going to look into validating uniqueness of a given column based on two or more columns on a database level for data integrity.

Lets create a simple e-commerce app

```bash
rails new e-com -d=postgresql --api
```

We will create 4 models **Product, Cart, CartItem, Sku**

```bash
rails g model sku no:string

rails g model product name:string sku:references

rails g model cart

rails g model cart_item quantity:integer cart:references sku:references product:references

rake db:create
rake db:migrate
```

Great! We now have our models

**Understanding Associations**
1. **Product**
   - Belongs to an **sku** and an **sku** has one **product**, in short it has **one_to_one** relationship.

    ```ruby
    class Product < ApplicationRecord
      belongs_to :sku
    end
    ```

2. **Sku**
   - Has one product
    ```ruby
    class Sku < ApplicationRecord
      has_one :product
    end
   ```

3. **Cart**
   - Has many **cart items**, and a **CartItem** belongs to a cart. The association is **one_to_many**
    ```ruby
    class Cart < ApplicationRecord
      has_many :cart_items
    end
    ```

4. **CartItem**
   - Belongs to **cart, product, and sku**
    ```ruby
    class CartItem < ApplicationRecord
      belongs_to :cart
      belongs_to :sku
      belongs_to :product
    end
    ```

**Goal**
Our goal is to have one **cart_item** of a given **product_id** within a given **cart** and also ensure that the **sku_id** is unique.

If we create a **cart_item** record with **cart_id=1, product_id=1 and sku_id=1**, then we should never have another record with **cart_id=1, product_id=1 and sku_id=1**. Make sense?

Let's see it in action, fire up rails console

```bash
rails c
```

Add records
```ruby
Sku.create

Product.create sku: Sku.first, name: 'Product 1'

Cart.create
```

Output:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/umyadr9u0odkzegr6bpd.png)

Create First **CartItem**
```ruby
CartItem.create product: Product.first, sku: Product.first.sku, cart: Cart.first, quantity: 1
```

Create another **CartItem** record.
```ruby
CartItem.create product: Product.first, sku: Product.first.sku, cart: Cart.first, quantity: 1
```

**Pssst!** This shouldn't work, we run the same command, now we have 2 cart items with the same **cart_id, product_id, and sku_id**. How can we fix this?

1. Delete all cart items
    ```ruby
    CartItem.destroy_all
    ```

2. Add **unique index constraint**
    ```bash
    rails g migration add_unique_index_to_cart_items
    ```

3. Open the migration file and add the statement below.
    ```ruby
    add_index :cart_items, [:cart_id, :product_id, :sku_id], unique: true
    ```

4. Run migrations
    ```bash
    rake db:migrate
    ```

5. Open rails console and create 2 cart items with the same **cart_id, product_id and sku_id**
    ```rails
    CartItem.create product: Product.first, sku: Product.first.sku, cart: Cart.first, quantity: 1

    CartItem.create product: Product.first, sku: Product.first.sku, cart: Cart.first, quantity: 1
    ```

We did it! you will notice that the second command is rolled back! This is because of the constrain that we added.

Now lets validate by adding a different product.

```rails
Sku.create

Product.create sku: Sku.last, name: 'Product 2'
```

Add the above product to the same cart
```rails
CartItem.create product: Product.last, sku: Product.last.sku, cart: Cart.first, quantity: 1
```

The above works because, the **product_id** and **sku_id** are unique, even though they are in the same cart.

Query **Cart** items
```ruby
Cart.first.cart_items
```

Output

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fnlvic5upuj5eqmg5l11.png)

**Takeaways**
1. Do not rely on model level validations, as they can be bypassed.
2. Supplement model level validations with database level validations.