Ruby-on-rails 向列组合添加唯一约束的迁移
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3370271/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
A migration to add unique constraint to a combination of columns
提问by rangalo
What I need is a migration to apply unique constraint to a combination of columns. i.e. for a peopletable, a combination of first_name, last_Nameand Dobshould be unique.
我需要的是将唯一约束应用于列组合的迁移。即对于people表的组合first_name，last_Name而Dob应该是唯一的。
回答by Robert Speicher
add_index :people, [:firstname, :lastname, :dob], :unique => true
add_index :people, [:firstname, :lastname, :dob], :unique => true
回答by A Fader Darkly
According to howmanyofme.com, "There are 46,427 people named John Smith" in the United States alone. That's about 127 years of days. As this is well over the average lifespan of a human being, this means that a DOB clash is mathematically certain.
据 howmanyofme.com 报道，仅在美国，“就有 46,427 人名为约翰·史密斯”。这大约是 127 年的天数。由于这远远超过了人类的平均寿命，这意味着 DOB 冲突在数学上是确定的。
All I'm saying is that that particular combination of unique fields could lead to extreme user/customer frustration in future.
我要说的是，独特字段的特定组合可能会导致未来的用户/客户极度沮丧。
Consider something that's actually unique, like a national identification number, if appropriate.
如果合适，请考虑一些实际上独一无二的东西，例如国家身号码。
(I realise I'm very late to the party with this one, but it could help future readers.)
（我意识到我参加这个派对已经很晚了，但它可以帮助未来的读者。）
回答by Josh
You may want to add a constraint without an index. This will depend on what database you're using. Below is sample migration code for Postgres. (tracking_number, carrier)is a list of the columns you want to use for the constraint.
您可能想要添加一个没有索引的约束。这将取决于您使用的数据库。以下是 Postgres 的示例迁移代码。 (tracking_number, carrier)是要用于约束的列的列表。
class AddUniqeConstraintToShipments < ActiveRecord::Migration
def up
execute <<-SQL
alter table shipments
add constraint shipment_tracking_number unique (tracking_number, carrier);
SQL
end
def down
execute <<-SQL
alter table shipments
drop constraint if exists shipment_tracking_number;
SQL
end
end
There are different constraints you can add. Read the docs
您可以添加不同的约束。 阅读文档
回答by Bohdan
Hi You may add unique index in your migration to the columns for example
嗨，例如，您可以在迁移中向列添加唯一索引
add_index(:accounts, [:branch_id, :party_id], :unique => true)
or separate unique indexes for each column
或为每列单独的唯一索引
回答by Dorian
In the typical example of a join table between users and posts:
在用户和帖子之间的连接表的典型示例中：
create_table :users
create_table :posts
create_table :ownerships do |t|
t.belongs_to :user, foreign_key: true, null: false
t.belongs_to :post, foreign_key: true, null: false
end
add_index :ownerships, [:user_id, :post_id], unique: true
Trying to create two similar records will throw a database error (Postgres in my case):
尝试创建两个相似的记录将引发数据库错误（在我的情况下为 Postgres）：
ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "index_ownerships_on_user_id_and_post_id"
DETAIL: Key (user_id, post_id)=(1, 1) already exists.
: INSERT INTO "ownerships" ("user_id", "post_id") VALUES (, ) RETURNING "id"
e.g. doing that:
例如这样做：
Ownership.create!(user_id: user_id, post_id: post_id)
Ownership.create!(user_id: user_id, post_id: post_id)
Fully runnable example: https://gist.github.com/Dorian/9d641ca78dad8eb64736173614d97ced
完全可运行的例子：https: //gist.github.com/Dorian/9d641ca78dad8eb64736173614d97ced
db/schema.rbgenerated: https://gist.github.com/Dorian/a8449287fa62b88463f48da986c1744a
db/schema.rb生成：https: //gist.github.com/Dorian/a8449287fa62b88463f48da986c1744a
回答by Khalil Gharbaoui
For completeness sake, and to avoid confusion here are 3 ways of doing the same thing:
Adding a named unique constraint to a combination of columns in Rails 5.2+
为了完整起见，并避免混淆，这里有 3 种方法可以做同样的事情：
在 Rails 5.2+ 中向列组合添加命名的唯一约束
Let's say we have Locations table that belongs to an advertiser and has column reference_code and you only want 1 reference code per advertiser. so you want to add a unique constraint to a combination of columns and name it.
假设我们有一个属于广告商的 Locations 表，并且有列 reference_code，并且您只需要每个广告商 1 个参考代码。所以您想向列组合添加唯一约束并为其命名。
Do:
做：
rails g migration AddUniquenessConstraintToLocations
rails g migration AddUniquenessConstraintToLocations
And make your migration look either something like this one liner:
并使您的迁移看起来像这样的一个班轮：
class AddUniquenessConstraintToLocations < ActiveRecord::Migration[5.2]
def change
add_index :locations, [:reference_code, :advertiser_id], unique: true, name: 'uniq_reference_code_per_advertiser'
end
end
OR this block version.
或者这个块版本。
class AddUniquenessConstraintToLocations < ActiveRecord::Migration[5.2]
def change
change_table :locations do |t|
t.index ['reference_code', 'advertiser_id'], name: 'uniq_reference_code_per_advertiser', unique: true
end
end
end
OR this raw SQL version
或者这个原始 SQL 版本
class AddUniquenessConstraintToLocations < ActiveRecord::Migration[5.2]
def change
execute <<-SQL
ALTER TABLE locations
ADD CONSTRAINT uniq_reference_code_per_advertiser UNIQUE (reference_code, advertiser_id);
SQL
end
end
Any of these will have the same result, check your schema.rb
其中任何一个都会有相同的结果，请检查您的 schema.rb
