在PostgreSQL表中更改主键

Question

我的PostgreSQL 9.3.6数据库中有一个名为users的表格，其中包含两列：id和another_id。 id是主键，another_id是另一个带有唯一约束条件的整数列。

还有其他表格通过主键引用用户。

以下是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

这是 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

如何将PostgreSQL表中的主键从id列替换为another_id列，并保持数据完整性？

- Slava Fomin II

---

您可以使用现有的唯一索引 (uniq_1483a5e93414710b) 创建主键，这将切换到新的 PK。 - Vao Tsun

顺便提一下，这将需要重建所有引用的FK。至于完整性方面，PG允许您在事务中运行DDL。因此，在BEGIN; END;之间运行整个DDL集合。 - Vao Tsun

你想要完全删除 id 列，并用 another_id 替换它，还是两者都要保留？如果两者都要，请将主键“重定向”到 another_id 还是不需要？ - MatheusOl

Answer 1

我花了一些时间，最终想出了一个有效的解决方案。我将在这里发布它，以供将来参考。

解决方案

首先，您有三个表（foo_table、bar_table、baz_table），它们通过外键（在所有情况下称为user_id）指向您的users表。 您需要将存储在这些列中的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;

您需要针对每个从属表重复上述查询。

之后，所有从属表都将指向您的新another_id列。

最后，我们只需替换主键：

-- 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

如果您愿意，甚至可以删除原始的id列。

希望这能对某些人有所帮助。

- Slava Fomin II

---

这些查询不应该在一个事务中运行以维护完整性吗？ - Masked Man