Enforcing Unique Constraints Across Multiple Columns in Ruby on Rails

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

1. 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
   

2. Sku

   • Has one product

   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

   class Cart < ApplicationRecord
     has_many :cart_items
   end
   

4. 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?

1. Delete all cart items
   

   CartItem.destroy_all
   

2. Add unique index constraint
   

   rails g migration add_unique_index_to_cart_items
   

3. Open the migration file and add the statement below.
   

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

4. Run migrations
   

   rake db:migrate
   

5. 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

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

Comments

[Harry Wood]

"Our goal is to ensure that we can only have one cart_item within a given cart"

I think maybe this should have said "one cart_item of a given product_id within a given cart"

[Gideon Kimutai]

Thank you @harry_wood for that pointer.