Auto increment in phpmyadmin

Question

I have an existing database using PHP, MySQL and phpMyAdmin.

When users become a member on my website, I need the system to create a unique membership number for them using a five digit number. for e.g 83773. I guess it is like generating a random password except I only want numbers for my members. This ID number has to be unique to each member.

Would it be possible for me to set the primary key to auto_increment in my user table and set it to start at 10000 and then auto increment every time a member registers?

Also, is there a maximum number that the primary key ID number would go up to?

Is this a reliable and safe way to use the primary key ID number as a membership number?

Some of the people below answered that there is no upper limit to the primary key, but I would assume that the upper limit is the maximum size of the data type you selected...maybe I'm wrong, but wouldn't int be limited to a max value of 4294967295 then? http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html — Sam, Apr 14 '11 at 16:19
Please pick up the accepted answer. I think this is the one http://stackoverflow.com/a/18942684/248616 — Nam G VU, Oct 20 '14 at 07:36

score 262 · Answer 1 · edited May 13 '20 at 17:49

262

There are possible steps to enable auto increment for a column. I guess the phpMyAdmin version is 3.5.5 but not sure.

Click on Table > Structure tab > Under Action Click Primary (set as primary), click on Change on the pop-up window, scroll left and check A_I. Also make sure you have selected None for Default enter image description here

edited May 13 '20 at 17:49

Viktor Borítás

135
2
11

answered Sep 22 '13 at 10:10

Amit Bhagat

4,182
3
23
24

5

Since the auto-inc feature was removed from the Operations tab, it appears this is how to implement it now. Thanks, @AmitKB. – Mike S. Oct 01 '13 at 19:03
2

However when I tried that I got this error: `Query error: #1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key` – yaakov Feb 07 '16 at 15:47
The message is clear, you might be trying to have more than one auto column. – Amit Bhagat Feb 08 '16 at 03:19
However when I tried that I got this error: `Query error: #1062 - ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'` – Casper Jul 13 '16 at 02:22
Looks like that columns already have values, you can remove values from that column and try it. – Amit Bhagat Jul 13 '16 at 12:11
1

Had a similar issue and this fixed my problem – Callat Sep 04 '17 at 15:44
1

wow, so simple. why has nobody mentioned this? took me so long to find an answer like this. by far the best answer. – oldboy Sep 15 '17 at 01:16
Yaakov Ainspan set primary key first then set auto increment – Clark Superman Nov 20 '18 at 08:15
It requires to set the column as key. – Henry Dec 02 '18 at 05:14
@YaakovAinspan Make sure your "Attributes" i.e. Table > Structure tab > Action > Change > Attributes is set to "Empty". If it is not, then first make it Empty, save it, and then redo your action and enable A_I check button and then save it. I got the same error i.e. "Query error: #1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key" but after I followed the above steps, it worked. – Ajay Bidyarthy Dec 16 '18 at 11:06
You can also uncheck the primary key option. – Ajay Bidyarthy Dec 17 '18 at 07:15

score 53 · Answer 2 · edited Aug 30 '14 at 16:08

53

In phpMyAdmin, navigate to the table in question and click the "Operations" tab. On the left under Table Options you will be allowed to set the current AUTO_INCREMENT value.

edited Aug 30 '14 at 16:08

Josh Crozier

233,099
56
391
304

answered Apr 14 '11 at 15:31

Mala

14,178
25
88
119

6

when i do this my only two options regarding adding auto_increment are "move table to" or "copy table to". i neither want to move or copy the table, but simply add auto_increment. what is going on?? i'm using MySQL v5.6. – oldboy Sep 15 '17 at 01:15
1

@oldboy I get the same as you. Perhaps they removed the feature in newer versions of XAMPP PhpMyAdmin – KD_Raj Jul 02 '20 at 12:38

lethalMango · Answer 3 · 2011-04-14T15:42:42.250

Just run a simple MySQL query and set the auto increment number to whatever you want.

ALTER TABLE `table_name` AUTO_INCREMENT=10000

In terms of a maximum, as far as I am aware there is not one, nor is there any way to limit such number.

It is perfectly safe, and common practice to set an id number as a primiary key, auto incrementing int. There are alternatives such as using PHP to generate membership numbers for you in a specific format and then checking the number does not exist prior to inserting, however for me personally I'd go with the primary id auto_inc value.

Phil · Answer 4 · 2015-02-20T04:47:34.317

13

In "Structure" tab of your table
Click on the pencil of the variable you want auto_increment
under "Extra" tab choose "auto_increment"
then go to "Operations" tab of your table
Under "Table options" -> auto_increment type -> 10000

edited Feb 20 '15 at 04:47

answered Feb 20 '15 at 04:41

Phil

159
1
6

score 2 · Answer 5 · answered May 22 '17 at 14:32

2

@AmitKB, Your procedure is correct. Although this error

Query error: #1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

can be solved by first marking the field as key(using the key icon with label primary),unless you have other key then it may not work.

answered May 22 '17 at 14:32

KE Keronei

323
2
8

It has been so long since I played with phpMyAdmin I forgot how to do this. Thanks for the fix to this secondary issue. It worked for me. – Ryan Aug 21 '19 at 23:58

score 2 · Answer 6 · answered Apr 14 '11 at 15:31

2

In phpMyAdmin, if you set up a field in your table to auto increment, and then insert a row and set that field's value to 10000, it will continue from there.

answered Apr 14 '11 at 15:31

Tim

749
4
14

score 2 · Answer 7 · answered Jul 01 '21 at 19:35

2

The easiest way to do this on the latest phpmyadmin is by using the following command;

ALTER TABLE `table_name` ADD `col_name` INT(1) NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`col_name`);

answered Jul 01 '21 at 19:35

Web Dev

134
11

score 1 · Answer 8 · answered Feb 10 '20 at 06:14

(a)Simply click on your database, select your table. Click on 'Operations'. Under the 'table options' section change the AUTO_INCREMENT value to your desired value, in this case: 10000 the click 'Go'. (See the image attached)

(b)Alternatively, you can run a SQL command under the SQL tab after selecting your table. Simply type 'ALTER TABLE table_name AUTO_INCREMENT = 10000;' then click 'Go'. That's it!! SETTING AUTO INCREMENT VALUE image(a)

SETTING AUTO INCREMENT VALUE image(B)

score 0 · Answer 9 · answered Apr 14 '11 at 15:37

You cannot set a maximum value (other than choosing a datatype which cannot hold large numbers, but there are none that have the limit you're asking for). You can check that with LAST_INSERT_ID() after inserting to get the id of the newly created member, and if it is too big handle it in your application code (e.g., delete and reject the member).

Why do you want an upper limit?

score 0 · Answer 10 · edited Jul 14 '19 at 10:10

0

This is due to the wp_terms, wp_termmeta and wp_term_taxonomy tables, which had all their ID's not set to AUTO_INCREMENT

To do this go to phpmyadmin, click on the concern database, wp_terms table, click on structure Tab, at right side you will see a tab named A_I(AUTO_INCREMENT), check it and save (You are only doing this for the first option, in the case wp_term you are only doing it for term_id).

Do the same for wp_termmeta and wp_term_taxonomy that will fix the issue.

edited Jul 14 '19 at 10:10

HMD

2,202
6
24
37

answered Jul 14 '19 at 09:26

Faith Olanrewaju

1
1

It seems like you think the OP is using WordPress, where did you find that information? I cannot find any references to WP in the question. – Raul Sauco Jul 17 '19 at 01:52

Auto increment in phpmyadmin

10 Answers10

Linked