将主键改为自增。

Question

我有一个名为Player的表，其中有一个字符型(7)主键playerID。我已经在这个表中有一些记录，并且还有一些其他的表，它们将playerID作为外键，并且这些表也已经有了一些记录。

如何设置playerID自增？经过一段时间的阅读后，我认为我应该从一开始就这样做，但是现在我不能这样做，是否有任何方法可以实现它？

例如，当我运行这个时：

ALTER TABLE player ADD COLUMN key_column BIGSERIAL PRIMARY KEY;

它返回一个错误：

ERROR: multiple primary keys for table "player" are not allowed

如果我放弃现有的playerID，引用它的其他表中的记录也将被删除。

是否有一种方法可以将现有的主键playerID更改为自动递增？

- Chin

Answer 1

我想到了一个解决办法：只需将默认值设置为自动增量的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

- Chin

---

我使用了一个更加健壮的查询来确定最大值： select setval('player_id_seq', (select max(player_id_seq) from player) + 1); - aqteifan

Answer 2

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

输出：

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 | 

- wildplasser

Answer 3

我认为在一个表中你不能有两个主键，而且因为playerID的数据类型是字符型(7)，我认为你不能将其更改为自动增量。所以，如果你想添加一个新的主键，我相信你必须删除playerID的主键约束。

由于你已经有了数据并且在其他表中使用playerID作为外键，我建议你复制你的player表，并在第二个表上测试这些更改，以避免损坏你的数据。

但在尝试所有这些之前，请确保你正在使用创建更改的相同的数据库用户。

- Ferox

Answer 4

以下代码将在PGSQL中为现有列设置自动增量：

ALTER TABLE schema.table ALTER COLUMN id 
  SET DEFAULT nextval('schema.table_id_seq'::regclass);

                                      

- Claudio Melo

---

请正确格式化您的代码，点击此处了解如何操作。 - rizerphe

抱歉在评论中提问，@БогданОпир，但我不知道其他方法。你的评论是针对原始答案吗？还是在我编辑后代码仍然格式错误？ - Sigfried

@Sigfried 这是针对 OP 的。你不需要再更改它了。然而，最好仍然使用三个反引号。 - rizerphe

谢谢，@БогданОпир。我注意到答案中有一个错别字，已经修正了。由于编辑时有奇怪的6个字符最小限制，我尝试切换到三个反引号，但是这样就失去了语法高亮。我在顶部反引号后面尝试使用{lang=sql}（并拼写出语言名称），但这并没有帮助。你链接的页面没有提到三个反引号。 - Sigfried

@Sigfried 默认情况下，它每10秒更新一次预览语法高亮。 - rizerphe