更改PostgreSQL表中的主键 [英] Change primary key in PostgreSQL table

问题描述

我的PostgreSQL 9.3.6 数据库中有个用户表，其中有两列： id 和 other_id 。 id 是主键， otherother_id 只是另一个具有唯一约束的整数列。

I have users table in my PostgreSQL 9.3.6 database with two columns: id and another_id. The id is a primary key, the another_id is just another integer column with unique constraint.

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

There are other tables that reference users by primary key.

这里是个用户表说明：

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 列并维护数据完整性？

How do I replace primary key in PostgreSQL table from id column to another_id column and maintain data integrity?

推荐答案

我花了一段时间，终于想出了一个可行的解决方案。

I've spent some time and finally came up with a working solution.

我将在此处发布

首先，您有三个表（ foo_table ， bar_table ， baz_table ）指向您的用户表通过外键（在所有情况下都称为 user_id ）。您需要将存储在这些列中的ID从 id 替换为 another_id 。这是您的操作方法：

First of all, you have three tables (foo_table, bar_table, baz_table) which are pointing to your users table by means of foreign keys (called user_id in all cases). You will need to replace the IDs stored in those columns from id to another_id. Here's how you can do it:

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

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

After that, all dependent tables will point to your new another_id column.

在最后，我们只需要替换主键即可：

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

如果需要，甚至可以删除原始的 id 列。

You can even drop the original id column if you want to.

我希望它会帮助某人。

这篇关于更改PostgreSQL表中的主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！