Error changing MySQL column to AUTO_INCREMENT

Question

Here is my current table design:

enter image description here

I have several columns and no KEYs set, so when I try the following command:

ALTER TABLE users MODIFY COLUMN id INT auto_increment;

it gives me an error. I want the id column in the table to auto-increment and I want it to start with 1.

how about `alter table users change id id int primary KEY AUTO_INCREMENT;` — Abhik Chakraborty, Dec 07 '14 at 21:19
@Nordenheim In SQL Fiddle the error returns `Schema Creation Failed: Incorrect table definition; there can be only one auto column and it must be defined as a key:`. — AdamMc331, Dec 07 '14 at 22:40

score 1 · Answer 1 · edited May 23 '17 at 12:21

1

I think you are missing the primary key definition. Try this:

ALTER TABLE users MODIFY id INT AUTO_INCREMENT PRIMARY KEY

Also, I recommend you check this out.

edited May 23 '17 at 12:21

Community

answered Dec 07 '14 at 21:20

markus

1

Good answer. This should work. I think it's also important to mention that the reason this is required is because a column *CANNOT* be auto_increment if it is not a Primary Key. In OP's case, id is not a key, so that is why it cannot be altered to auto_increment alone. – AdamMc331 Dec 07 '14 at 22:37
Also, a funny thing to note (since you asked in the comments) I believe the error OP is getting is this: `Schema Creation Failed: Incorrect table definition; there can be only one auto column and it must be defined as a key: ` – AdamMc331 Dec 07 '14 at 22:41

score 0 · Answer 2 · answered Dec 07 '14 at 23:09

0

You must specify PRIMARY_KEY with AUTO_INCREMENT:

ALTER TABLE users MODIFY id INT PRIMARY_KEY AUTO_INCREMENT;

answered Dec 07 '14 at 23:09

Kevin Kopf

2 Answers2