postgresql - activerecord unique constraint on multiple records

Question

how should I write the activerecord migration to reflect this :

CREATE TABLE table (
    c1 data_type,
    c2 data_type,
    c3 data_type,
    UNIQUE (c2, c3)
);

This adds a unique constraint on one column, but what I'm looking for is to create the unique constraint on the combination of 2 columns, like explained in the section Creating a UNIQUE constraint on multiple columns.

EDIT

More precisely: I have a table account and a table balance_previous_month.

class CreateBalance < ActiveRecord::Migration[6.1]
  def change    
        create_table :balance_previous_month do |t|
        t.decimal :amount, :precision => 8, :scale => 2
        t.date :value_date
        t.belongs_to :account, foreign_key: true
        t.timestamps
    end
  end
end

Since we're in January, the value date (i.e. balance at the end of the previous month) is 2020-12-31.

I want to put a constraint on the table balance_previous_month where per account_id, there can be only one value_date with a given amount. The amount can be updated, but a given account can't have 2 identical value_dates.

question from:https://stackoverflow.com/questions/65897578/activerecord-unique-constraint-on-multiple-records

Answer 1

The link you added to the other post is not exactly equivalent to your request since one answer talks about enforcing uniqueness through the model while the other talks about using an index while in your example you are using a constraint. (Check this for more information on the difference between them).

There are 2 places where you can enforce uniqueness, application and database and it can be done in both places at the same time as well.

Database

So if you want to enforce uniqueness by using an index you can use this:

def change
  add_index :table, [:c2, :c3], unique: true
end

If you want to add a constraint as in your example you will have to run a direct sql query in your migration as there is no built-in way in rails to do that.

def up
  execute <<-SQL
    ALTER TABLE table
    ADD UNIQUE (c2, c3)
  SQL
end

Check the link above for more info about the difference between them.

Application

Enforcing uniqueness through the model:

validates :c2, uniqueness: { scope: :c3 }