asked on

How to make a column auto increment using phpmyadmin

I need to make a table column auto increment. I have to use phpMyadmin, I do not have direct MySQL access.
I found this by google searching:

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.

There is no "Extra" tab.

How can I do this?

Ray Paseur

phpMyAdmin comes in various versions with different links and capabilities. Is there a URL where we can see this in action? I might be able to figure it out, but I can't guess without at least seeing the browser window.

Richard Korts

ASKER

Ray,

I can give you the server credentials; it's a WordPress environment & the host is WPEngine.

Can you give me a private message area where I can send you that?

Thanks,

Richard

Dave Baldwin

From http://dev.mysql.com/doc/refman/5.7/en/create-table.html

There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers “wrap” over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0.

Richard Korts

ASKER

Dave,

Does it have to be indexed BEFORE you make it auto increment?

Thanks

Ray Paseur

I always define my index columns INT NOT NULL AUTO_INCREMENT PRIMARY KEY and it works perfectly every time.

My email address is shown in my E-E profile:
https://www.experts-exchange.com/members/Ray_Paseur.html

ASKER CERTIFIED SOLUTION

Dave Baldwin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Richard Korts

ASKER

I tried this, it failed: ALTER TAble "categories" MODIFY COLUMN "catID" AUTO-Increment

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"categories" MODIFY COLUMN "catID" AUTO-Increment' at line 1

How do I make it right??>

SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kim Walker

Your table name and column names should be wrapped in back ticks or not at all. Wrapping them in quotes makes them a string instead of a table or column. And, as Ray has pointed out, it should probably be AUTO_INCREMENT.

Alternatively, in my version of phpMyAdmin, when you click the pencil to modify the column parameters, there's a checkbox under the label A_I for setting auto_increment. Check that box and be sure the column is indexed.

Making a column auto_increment does NOT change the values that are already there. It simply changes the default value of that column for new rows. So if you try to make the column UNIQUE or PRIMARY it will likely fail until you alter the existing data to have unique values. Then under the Operations tab/Table options, you need to set your auto_increment value to something higher than any value in the existing data. Otherwise you will run into a conflict when you attempt to add a row where the auto_increment value matches an existing value. But this for UNIQUE or PRIMARY columns only.

BTW, there should be an image button under the phpMyAdmin logo in the top left corner which opens up the phpMyAdmin documentation for your specific version.