更改 PostgreSQL 表中的主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29075413/
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
Change primary key in PostgreSQL table
提问by Slava Fomin II
I have userstable in my PostgreSQL 9.3.6database with two columns: idand another_id. The idis a primary key, the another_idis just another integer column with unique constraint.
我users在我的PostgreSQL表9.3.6的数据库有两列：id和another_id。这id是一个主键，这another_id只是另一个具有唯一约束的整数列。
There are other tables that reference users by primary key.
还有其他表通过主键引用用户。
Here's the userstable description:
以下是users表格说明：
Table "public.users"
Column | Type | Modifiers | Storage | Stats target | Description
----------------------+--------------------------------+----------------------------------------+---------+--------------+-------------
id | integer | not null | plain | |
another_id | integer | not null | plain | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"uniq_1483a5e93414710b" UNIQUE, btree (another_id)
Referenced by:
TABLE "foo_table" CONSTRAINT "fk_4affc6e5a76ed395" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
TABLE "bar_table" CONSTRAINT "fk_72936b1da76ed395" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
TABLE "baz_table" CONSTRAINT "fk_83adbaf0a76ed395" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
Here's foo_tabledescription:
下面是foo_table说明：
Table "public.foo_table"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+--------------------------------+-----------------------------------------------+----------+--------------+-------------
id | integer | not null | plain | |
user_id | integer | | plain | |
Indexes:
"foo_table_pkey" PRIMARY KEY, btree (id)
"idx_e52ffdeea76ed395" btree (user_id)
Foreign-key constraints:
"fk_e52ffdeea76ed395" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
How do I replace primary key in PostgreSQL table from idcolumn to another_idcolumn and maintain data integrity?
如何从id列到another_id列替换 PostgreSQL 表中的主键并保持数据完整性？
回答by Slava Fomin II
I've spent some time and finally came up with a working solution.
我花了一些时间，终于想出了一个可行的解决方案。
I will publish it here for future reference.
我将在这里发布以供将来参考。
Solution
解决方案
First of all, you have three tables (foo_table, bar_table, baz_table) which are pointing to your userstable by means of foreign keys (called user_idin all cases). You will need to replace the IDs stored in those columns from idto another_id. Here's how you can do it:
首先，您有三个表 ( foo_table, bar_table, baz_table)，它们users通过外键（user_id在所有情况下都调用）指向您的表。您需要将存储在这些列中的 ID 从 替换id为another_id。您可以这样做：
-- We are dropping the foreign key constraint on dependant table (in other case it will prevent us from updating the values)
ALTER TABLE foo_table DROP CONSTRAINT fk_e52ffdeea76ed395;
-- Then, we're swapping values in foreign key column from id to another_id
UPDATE foo_table T SET user_id = (SELECT another_id FROM users WHERE id = T.user_id);
-- And finally we're creating new foreign key constraint pointing to the another_id instead of id
ALTER TABLE foo_table ADD CONSTRAINT fk_e52ffdeea76ed395 FOREIGN KEY (user_id) REFERENCES users (another_id) ON DELETE CASCADE;
You will need to repeat the above queries for each dependent table.
您将需要对每个从属表重复上述查询。
After that, all dependent tables will point to your new another_idcolumn.
之后，所有依赖表都将指向您的新another_id列。
In the end we will just need to replace the primary key:
最后，我们只需要替换主键：
-- 1. Dropping the original primary key
ALTER TABLE users DROP CONSTRAINT users_pkey
-- 2. Renaming existing index for another_id (optional)
ALTER INDEX uniq_1483a5e93414710b RENAME TO users_pkey
-- 3. Creating new primary key using existing index for another_id
ALTER TABLE users ADD PRIMARY KEY USING INDEX users_pkey
-- 4. Creating index for old id column (optional)
CREATE UNIQUE INDEX users_id ON users (id)
-- 5. You can drop the original sequence generator if you won't need it
DROP SEQUENCE users_id_seq
You can even drop the original idcolumn if you want to.
id如果您愿意，您甚至可以删除原始列。
I hope it will help someone.
我希望它会帮助某人。
