How to add auto-increment to column in mysql database using phpmyadmin?

Question

I've been trying to add auto-increment to one of my columns (basically an ID) but I can't find the auto-increment option for my column. Any idea where it is?

score 23 · Answer 1 · answered Nov 13 '15 at 05:51

23

To use the GUI:

Click the STRUCTURE tab to see the list of existing fields

To set a field as the PRIMARY FIELD, click the gold key -- it will turn silver.

To set a field (usually the same field) as auto-increment:
a. Click CHANGE for that field
b. Look to the far right and checkmark the AI box
c. Click SAVE button

answered Nov 13 '15 at 05:51

cssyphus

37,875
18
96
111

bingo - you saved my day – Ben Apr 16 '16 at 11:50
A_I? That is very much NOT OBVIOUS at all. But this is how I got it to work. Thanks! – Josh P May 03 '17 at 22:14
1

Observe that A_I stands for AUTO_INCREMENT. – João Víctor Melo Apr 07 '22 at 19:32

score 22 · Accepted Answer · answered Oct 17 '13 at 18:51

22

You can add it like this

ALTER TABLE your_table
MODIFY some_column INT NOT NULL AUTO_INCREMENT;

answered Oct 17 '13 at 18:51

juergen d

201,996
37
293
362

FredHead · Answer 3 · 2015-09-15T23:58:05.393

5

A couple quick points based on recent experience:

To the original question, how to select auto-increment with phpmyadmin, it's the small AI check box on the change screen for a field name.
When I tried the "ALTER TABLE tablew_name CHANGE id id BIGINT(20) NOT NULL AUTO_INCREMENT;" solution above, phpmyadmin gave me an error message saying the field had to have a key. I selected a Unique key and the error message went away and the field now auto increments.

edited Sep 15 '15 at 23:58

answered Sep 15 '15 at 16:56

FredHead

218
2
7

Your answer is incorrect, and as much as you want to help - it's misleading. Only a primary key can be auto_increment, and primary key is unique - therefore your point number 2. is inaccurate. I won't downvote but could you adjust your answer so it's closer to accurate? – N.B. Sep 15 '15 at 16:58
As noted in my edit, my answer was correct, as far as I can tell, in that selecting a Unique key in phpmyadmin removed an error message that prevented using one of the solutions above. I was not commenting on the relative merits of unique keys vs primary keys or any similarities/differences/relationships between them, only pointing out my experience as noted in my original answer, an experience that may or may not be useful to the next person Googling for ideas on how to do this with phpmyadmin. – FredHead Sep 16 '15 at 00:00
Alright, your edited answer makes more sense now, up you go. – N.B. Sep 16 '15 at 16:35

score 4 · Answer 4 · answered Jul 11 '14 at 11:33

4

This wont work if there are any foreign keys defined, and that is very likely for id fields.

use:

ALTER TABLE tablew_name CHANGE id id BIGINT(20) NOT NULL AUTO_INCREMENT;

instead

answered Jul 11 '14 at 11:33

Oliver

117
1
5

score 0 · Answer 5 · edited Nov 29 '18 at 13:37

0

The SQL script is correct

ALTER TABLE your_table MODIFY some_column INT NOT NULL AUTO_INCREMENT;

but if you try so make before in the visual mode, with the mysql version 4.7.4, in the struct of the table

Appear when you create the table one option to say "A_I" , if you put your mouse appear message with AUTO_INCREMENT (The version of the foto is in Spanish version)

edited Nov 29 '18 at 13:37

roschach

8,390
14
74
124

answered Nov 29 '18 at 09:05

Miguel_J

43
6

score 0 · Answer 6 · answered Nov 07 '22 at 15:26

0

If you are using xampp:

step 1: Go to the structure tab.

step 2: click on change tab for the column you want to auto increment. In my case, i am changing for id column.

step 3: check the AI(autoincrement ) column

And you are done!

answered Nov 07 '22 at 15:26

Abhishek

546
5
13

How to add auto-increment to column in mysql database using phpmyadmin?

6 Answers6

If you are using xampp:

Linked