Creating an auto-increment column

[aigini82]

Hi,

I want to modify an existing table column to auto-increment, and I have tried these but I get error :

mysql> ALTER TABLE lz_chat_archive_dup2 MODIFY COLUMN chat_id INT(10) AUTO_INCREMENT;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

mysql> ALTER TABLE lz_chat_archive_dup2 MODIFY COLUMN chat_id INT(10) AUTO_INCREMENT KEY;
ERROR 1265 (01000): Data truncated for column 'chat_id' at row 12

How do I resolve this?

The reason I want it changed is because the last value in the column is 383932. I am going to insert more values into this table from another table, which has a duplicate structure (similar columns, etc). However, the column “chat_id” only has null values in the other table.
So when I insert values from that table to this one, the values should auto-increment to 383933 and so on.

Can this be done?

[m_hutley]

@aigini82

Data truncated for column 'chat_id' at row 12

whatever was in row 12 of your data, the database is telling you it wasnt an INT-able thing, so it’s done the best it could.

[r937]

a bit off topic, but i just thought i’d point out that the (10) in INT(10) has nothing to do with the size of the integers that can be inserted into the column – that size is the same for all INT columns, whether you specify INT(2) or INT(10) or INT(937)