DEV Community

Gideon Kimutai
Gideon Kimutai

Posted on • Updated on

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Add records

Sku.create

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

Cart.create
Enter fullscreen mode Exit fullscreen mode

Output:

Image description

Create First CartItem

CartItem.create product: Product.first, sku: Product.first.sku, cart: Cart.first, quantity: 1
Enter fullscreen mode Exit fullscreen mode

Create another CartItem record.

CartItem.create product: Product.first, sku: Product.first.sku, cart: Cart.first, quantity: 1
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

Add the above product to the same cart

CartItem.create product: Product.last, sku: Product.last.sku, cart: Cart.first, quantity: 1
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Output

Image description

Takeaways

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

Top comments (2)

Collapse
 
harry_wood profile image
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"

Collapse
 
gr1d99 profile image
Gideon Kimutai

Thank you @harry_wood for that pointer.