postgresql 将主键更改为自动递增
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15748473/
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 to auto increment
提问by Chin
I have a table Player, and a primary key playerID which is character(7). I have some records in this table already, and also have some other tables that has playerID as a foreign key, and those tables also have some records already.
我有一个表 Player 和一个主键 playerID，它是 character(7)。我在这个表中已经有一些记录，还有一些其他表将 playerID 作为外键，这些表也已经有一些记录。
How can I set the playerID to auto-increment? After reading for a while I think that I should have done this from the beginning, but since I can't do that now, is there anyway I can do it?
如何将 playerID 设置为自动递增？看了一会觉得应该一开始就做这个，但是现在做不到，有什么办法可以做吗？
For example, when I run this
例如，当我运行这个
ALTER TABLE player ADD COLUMN key_column BIGSERIAL PRIMARY KEY;
it returns an error:
它返回一个错误：
ERROR: multiple primary keys for table "player" are not allowed
and if I drop the existing playerID, records in other tables that reference it will be dropped as well.
如果我删除现有的 playerID，其他表中引用它的记录也将被删除。
Is there a way to "change" the existing primary key playerID to auto increment?
有没有办法“更改”现有的主键 playerID 以自动增加？
回答by Chin
I figure it out: just add an auto-increment default value to the playerID:
我想通了：只需向 playerID 添加一个自动递增的默认值：
create sequence player_id_seq;
alter table player alter playerid set default nextval('player_id_seq');
Select setval('player_id_seq', 2000051 ); --set to the highest current value of playerID
回答by wildplasser
DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;
-- create som data to play with
CREATE TABLE bagger
( player_id CHAR(6)
, tralala varchar
);
-- populate the table
INSERT INTO bagger(player_id,tralala)
SELECT gs::text, 'zzz_' || gs::text
FROM generate_series(1,10) gs
;
SELECT * FROM bagger;
--
-- create the sequence, change the datatype and bind it to the sequence
--
CREATE SEQUENCE player_id_seq;
ALTER TABLE bagger
ALTER COLUMN player_id TYPE INTEGER USING player_id::integer
, ALTER COLUMN player_id SET NOT NULL
, ALTER COLUMN player_id SET DEFAULT nextval('player_id_seq')
;
ALTER SEQUENCE player_id_seq
OWNED BY bagger.player_id
;
--
-- reset the sequence to containe the maximum occuring player_id in the table
--
SELECT setval('player_id_seq', mx.mx)
FROM (SELECT MAX(player_id) AS mx FROM bagger) mx
;
SELECT * FROM bagger;
\d bagger
Output:
输出：
DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
INSERT 0 10
player_id | tralala
-----------+---------
1 | zzz_1
2 | zzz_2
3 | zzz_3
4 | zzz_4
5 | zzz_5
6 | zzz_6
7 | zzz_7
8 | zzz_8
9 | zzz_9
10 | zzz_10
(10 rows)
CREATE SEQUENCE
ALTER TABLE
setval
--------
10
(1 row)
player_id | tralala
-----------+---------
1 | zzz_1
2 | zzz_2
3 | zzz_3
4 | zzz_4
5 | zzz_5
6 | zzz_6
7 | zzz_7
8 | zzz_8
9 | zzz_9
10 | zzz_10
(10 rows)
Table "tmp.bagger"
Column | Type | Modifiers
-----------+-------------------+-----------------------------------------------------
player_id | integer | not null default nextval('player_id_seq'::regclass)
tralala | character varying |
回答by Ferox
I don't think you can have 2 primary keys in one table, and because playerID data type is character(7) i don't think you can change it to auto increment.
我认为一张表中不能有 2 个主键，并且因为 playerID 数据类型是 character(7) 我认为你不能将其更改为自动递增。
So i believe you would have to remove the primary key constraint on playerID if you want to be able to add a new primary key.
因此，我相信如果您希望能够添加新的主键，则必须删除对 playerID 的主键约束。
Since you already have data on your table and you use playerID as a foreign key in other tables, i would advise you to duplicate your player table and test these changes on your second table to avoid corrupting your data.
由于您的表上已经有数据并且您在其他表中使用 playerID 作为外键，我建议您复制您的玩家表并在第二个表上测试这些更改，以避免损坏您的数据。
But before you try all this, make sure that you are trying to make this changes using the same db-user that created the tables you are changing
但在尝试所有这些之前，请确保您尝试使用创建要更改的表的同一个 db-user 进行此更改
