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
Regardless of using unique constraint or unique index, the field can accept null values, however the uniqueness will result in only accepting a single row with null value.
You can insert NULL values into columns with the UNIQUE constraint because NULL is the absence of a value, so it is never equal to other NULL values and not considered a duplicate value.
A null is never equal (nor not equal) to any other null, hence, a unique constraint on a single attribute will allow as many null rows as you can stuff in there.
The CREATE UNIQUE INDEX command creates a unique index on a table (no duplicate values allowed) Indexes are used to retrieve data from the database very fast. The users cannot see the indexes, they are just used to speed up searches/queries.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With