Alter table - adding AUTOINCREMENT in MySQL

Mastering AUTO_INCREMENT with ALTER TABLE

Understanding the ALTER TABLE keyword

Charging into battle doesn't mean you need to build a new fort (CREATE TABLE). You just need to fortify your existing castle (ALTER TABLE):

ALTER TABLE castle ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;


This equips your castle with a shiny new cannon that fires unique identities to new guests.

The epic showdown: MODIFY vs CHANGE keyword

In MySQL, the CHANGE and MODIFY keywords have distinct functions when used with ALTER TABLE. While MODIFY revises the column's datatype, CHANGE allows renaming and datatype modification:

ALTER TABLE tablename CHANGE columnname columnname datatype AUTO_INCREMENT;


Comment: Yes, that's right. CHANGE does it all. It's like a Swiss Army Knife.

Bending AUTO_INCREMENT to your will

To begin the AUTO_INCREMENT sequence at a chosen number, use:

ALTER TABLE tablename AUTO_INCREMENT = 100;


It's like saying, Nah, this party doesn't start until 100 walks in.

Specifying all modifiers: UNSIGNED and NOT NULL

While adding AUTO_INCREMENT, specifying the column as UNSIGNED ensures that only non-negative values are stored:

ALTER TABLE tablename CHANGE columnname columnname INT UNSIGNED AUTO_INCREMENT;


Comment: No negatives here, we're all about positivity.

Also, the column must be set to NOT NULL as AUTO_INCREMENT fields cannot be null:

ALTER TABLE tablename MODIFY columnname INT NOT NULL;
ALTER TABLE tablename CHANGE columnname columnname INT NOT NULL AUTO_INCREMENT;


Keeping syntax sharp and testing grounds

Wrap table and column names in backticks to prevent syntax conflicts:

ALTER TABLE `tablename` CHANGE `columnname` `columnname` INT AUTO_INCREMENT;


Remember, the backtick is mightier than the syntax error.

Always test your ALTER TABLE statements in a non-production environment first to safeguard your database's wellbeing.

Real-life scenarios and solutions

Renaming a column while adding AUTO_INCREMENT

Sometimes, you need to execute a double whammy—rename a column and add AUTO_INCREMENT:

ALTER TABLE tablename CHANGE old_col new_col INT AUTO_INCREMENT PRIMARY KEY;


Command translation: Out with the old, in with the new. Also, AUTO_INCREMENT, please.

Deleting AUTO_INCREMENT from a column

If you decide to oust AUTO_INCREMENT, no worries. Here's how to do it:

ALTER TABLE tablename MODIFY columnname INT;


Comment: AUTO_INCREMENT, you’re evicted.

Encounter AUTO_INCREMENT errors?

Remember, choosing an AUTO_INCREMENT value lower than the current max causes a duplicate entry for key 'PRIMARY' error. Always aim higher!