Menu
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.
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?
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.
Is there a way to "change" the existing primary key playerID to auto increment?
851
); The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record. Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5) .
There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. So you can indeed have an AUTO_INCREMENT column in a table that is not the primary key.
In MySQL, the syntax to reset the AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = value; table_name. The name of the table whose AUTO_INCREMENT column you wish to reset.
MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature. By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record. VALUES ('Lars','Monsen'); The SQL statement above would insert a new record into the "Persons" table.
I figure it out: just add an auto-increment default value to the 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 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 | If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
