title: Change primary key to auto increment
body: <p>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.</p>
  
  <p>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?</p>
  
  <p>For example, when I run this</p>
  
  <pre><code>ALTER TABLE player ADD COLUMN key_column BIGSERIAL PRIMARY KEY;
  </code></pre>
  
  <p>it returns an error:</p>
  
  <pre><code>ERROR: multiple primary keys for table "player" are not allowed
  </code></pre>
  
  <p>and if I drop the existing playerID, records in other tables that reference it will be dropped as well.</p>
  
  <p>Is there a way to "change" the existing primary key playerID  to auto increment?</p>