如何删除主键? [英] How to drop a primary key?

问题描述

所以，我想在我的 users 表上删除当前的主键.我认为它在 email 列上.

So, I'd like to drop the current primary key on my users table.. I think it's on the email column.

并在 uid 列中添加一个主键，以使我的 omniauth 正常工作.

And add a primary key to the uid column instead to get my omniauth working.

schema.rb 代码段

schema.rb snippit

  create_table "users", force: true do |t|
    t.string   "email",                  default: "", null: false
    t.string   "encrypted_password",     default: "", null: false
    t.string   "reset_password_token"
    t.datetime "reset_password_sent_at"
    t.datetime "remember_created_at"
    t.integer  "sign_in_count",          default: 0
    t.datetime "current_sign_in_at"
    t.datetime "last_sign_in_at"
    t.string   "current_sign_in_ip"
    t.string   "last_sign_in_ip"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.boolean  "admin"
    t.string   "provider"
    t.string   "uid"
    t.string   "username"
  end

我尝试使用以下方法向 uid 添加主键:

I've tried adding a primary key to uid with:

class ChangeUidToPrimaryKey < ActiveRecord::Migration
  def change
    execute 'ALTER TABLE users ADD PRIMARY KEY (uid)'
  end
end

并得到以下错误(代码段):

and got the following error (snippit):

PG::Error: ERROR:  multiple primary keys for table "users" are not allowed
: ALTER TABLE users ADD PRIMARY KEY (uid)/usr/local/rvm/gems/ruby-2.0.0-p195/gems/act

那么我怎样才能让它工作呢?

So how can I get this workin' ?

leap2_stage_development=# select * from information_schema.table_constraints where table_name='users';
   constraint_catalog    | constraint_schema |    constraint_name    |      table_catalog      | table_schema | table_name | constraint_type | is_deferrable | initially_deferred 
-------------------------+-------------------+-----------------------+-------------------------+--------------+------------+-----------------+---------------+--------------------
 leap2_stage_development | public            | users_pkey            | leap2_stage_development | public       | users      | PRIMARY KEY     | NO            | NO
 leap2_stage_development | public            | 2200_33435_1_not_null | leap2_stage_development | public       | users      | CHECK           | NO            | NO
 leap2_stage_development | public            | 2200_33435_2_not_null | leap2_stage_development | public       | users      | CHECK           | NO            | NO
 leap2_stage_development | public            | 2200_33435_3_not_null | leap2_stage_development | public       | users      | CHECK           | NO            | NO
(4 rows)

推荐答案

你必须先删除现有的主键，大致如下:

You have to drop the existing primary key first, something along the lines of:

ALTER TABLE "users" DROP CONSTRAINT "users_pkey".

您可以在迁移中的当前语句之前将该语句添加为另一个execute.

You can add that statement as another execute before your current one in the migration.

您可以使用以下内容检查表上的当前约束:

You can check the current constrains on a table with the following:

select * from information_schema.table_constraints wheretable_name='myTable';

来源:PostgreSQL 文档

这篇关于如何删除主键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！