Rails uniqueness constraint and matching db unique index for null column

Question

I have the following in my migration file

  def self.up
    create_table :payment_agreements do |t|
      t.boolean    :automatic, :default => true, :null => false
      t.string     :payment_trigger_on_order
      t.references :supplier
      t.references :seller
      t.references :product
      t.timestamps
    end
  end

I want to ensure that if a product_id is specified it is unique but I also want to allow null so I have the following in my model:

  validates :product_id,
            :uniqueness => true,
            :allow_nil => true

Works great but I should then add an index to the migration file

add_index :payment_agreements, :product_id, :unique => true

Obviously this will throw an exception when two null values are inserted for product_id. I could just simply omit the index in the migration but then there's the chance that I'll get two PaymentAgreements with the same product_id as shown here: Concurrency and integrity

My question is what is the best/most common way to deal with this problem

This question is similar to stackoverflow.com/questions/191421/… — x1a4
– x1a4, Commented May 28, 2010 at 5:30
validates_uniqueness_of:product_id, :if => lambda { !self.product_id.nil? } — user386660
– user386660, Commented Jul 9, 2010 at 10:11

zed_0xff · Accepted Answer · 2010-05-28 07:16:57Z

11

it depends on your db server. as for mysql:

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL.

answered May 28, 2010 at 7:16

zed_0xff

33.5k8 gold badges55 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rlandster · Accepted Answer · 2010-05-28 14:09:32Z

Some major database systems do not allow a unique index to contain multiple NULLs: unique applies to NULLs as well as non-NULLs. There are ways around this on the database level (e.g., triggers, or a computed column; see link text).

You could address this on an application level and put in a validation that checks for uniqueness if the product_id is not null.

validate :enforce_unique_product_id
def enforce_unique_product_id
  if (!self.product_id.nil? &&
      PaymentAgreement.exists?(:conditions=>['product_id = ?', self.product_id]))
    errors.add_to_base('There is already an agreement with product id " + 
                       self.product_id)
  end
end

(Update: As pointed out by zed_0xff, MySql allows multiple NULLs in a UNIQUE index in the most commonly used storage engines.)

Collectives™ on Stack Overflow

Rails uniqueness constraint and matching db unique index for null column

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related