向一组列添加唯一约束的迁移

What I need is a migration to apply unique constraint to a combination of columns. i.e. for a people table, a combination of first_name, last_Name and Dob should be unique.

我需要的是一个将唯一约束应用到列组合的迁移。例如，对于人员表，first_name、last_Name和Dob的组合应该是唯一的。

5 个解决方案

#1

196

add_index :people, [:firstname, :lastname, :dob], :unique => true

add_index:people， [:firstname，:lastname，:dob]，:unique => true

#2

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 * 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.)

(我意识到我参加这个聚会的时候已经很晚了，但它可以帮助未来的读者。)

#3

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

您可以添加不同的约束

#4

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

或为每一列单独的唯一索引

#5

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 ($1, $2) 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.rb generated: https://gist.github.com/Dorian/a8449287fa62b88463f48da986c1744a

db /模式。rb生成:https://gist.github.com/Dorian/a8449287fa62b88463f48da986c1744a

#1

196