Enforcing Unique Constraints Across Multiple Columns in Ruby on Rails

Gideon Kimutai — Thu, 05 Jan 2023 03:41:34 +0000

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

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

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

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

Product
- Belongs to an sku and an sku has one product, in short it has one_to_one relationship.
```
class Product < ApplicationRecord
  belongs_to :sku
end
```

Sku

Has one product

class Sku < ApplicationRecord
  has_one :product
end

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

CartItem

Belongs to cart, product, and sku

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

rails c

Add records

Sku.create

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

Cart.create

Output:

Create First CartItem

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

Create another CartItem record.

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?

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

Add unique index constraint

rails g migration add_unique_index_to_cart_items

Open the migration file and add the statement below.

add_index :cart_items, [:cart_id, :product_id, :sku_id], unique: true

Run migrations
```
rake db:migrate
```

Open rails console and create 2 cart items with the same cart_id, product_id and sku_id

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.

Sku.create

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

Add the above product to the same cart

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

Cart.first.cart_items

Output

Takeaways

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

Ongoing E-commerce shop API(Ruby on Rails)

Gideon Kimutai — Fri, 23 Dec 2022 15:36:43 +0000

Follow series here

Deploy Ruby on Rails App on AWS Part 1

Gideon Kimutai — Fri, 17 Jul 2020 04:27:59 +0000

Part 1: Setting up ec2 instance: https://youtu.be/-abUqrmB7Ns?list=PL3tDRmVst0DdgRzuIuN5dVb9uZtslUlLB

More parts to come.

Integrate warden to Ruby on Rails API only app(Very Basic)

Gideon Kimutai — Sat, 28 Dec 2019 18:27:09 +0000

Here is the link to the tutorial I made: start now

DEV Community: Gideon Kimutai

Enforcing Unique Constraints Across Multiple Columns in Ruby on Rails

Ongoing E-commerce shop API(Ruby on Rails)

Deploy Ruby on Rails App on AWS Part 1

Integrate warden to Ruby on Rails API only app(Very Basic)