Rails唯一性约束和空列的匹配数据库唯一索引 [英] Rails uniqueness constraint and matching db unique index for null column
问题描述
我的迁移文件中包含以下内容
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
我想确保如果指定了product_id,那么它是唯一的,但我也想允许null,因此我的模型中包含以下内容:
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
很显然,当为product_id插入两个null值时,这将引发异常.我可以简单地在迁移中省略索引,但是有机会获得具有相同product_id的两个PaymentAgreements,如下所示:
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
推荐答案
这取决于您的数据库服务器. 至于mysql:
it depends on your db server. as for mysql:
UNIQUE索引创建约束 这样索引中的所有值都必须 与众不同.如果您发生错误 尝试添加具有键值的新行 匹配现有行.这 约束不适用于NULL 值(BDB存储除外) 引擎.对于其他引擎,唯一 索引允许多个NULL值用于 可以包含NULL的列.
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.
这篇关于Rails唯一性约束和空列的匹配数据库唯一索引的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!