MySQL: I forgot to insert AUTO_INCREMENT

Book: PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide 5th Edition

Page: 133 Introduction to SQL.

2021 Today I forgot to insert insert AUTO_INCREMENT into the column?

Here is a solution:

MariaDB [sitename]> SHOW COLUMNS FROM users;
+-------------------+-----------------------+------+-----+---------+-------+
| Field             | Type                  | Null | Key | Default | Extra |
+-------------------+-----------------------+------+-----+---------+-------+
| user_id           | mediumint(8) unsigned | NO   | PRI | NULL    |       |
| first_name        | varchar(20)           | NO   |     | NULL    |       |
| last_name         | varchar(40)           | NO   |     | NULL    |       |
| email             | varchar(60)           | NO   |     | NULL    |       |
| pass              | char(128)             | NO   |     | NULL    |       |
| registration_date | datetime              | NO   |     | NULL    |       |
+-------------------+-----------------------+------+-----+---------+-------+
6 rows in set (0.016 sec)

As you can see I forgot to insert AUTO_INCREMENT at user_id under the column Extra.

I search the internet and tried different thing an in the end I got to this command:

ALTER TABLE users MODIFY COLUMN user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT;

This only works if your already logged in as a user or root

mysql -u root  

Step 1 - choose your database:

USE sitename

Step 2 - Alter the user_id to have AUTO_INCREMENT:

ALTER TABLE users MODIFY COLUMN user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT;

It will show:

Database changed
MariaDB [sitename]> ALTER TABLE users MODIFY COLUMN user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT;
Query OK, 0 rows affected (0.015 sec)
Records: 0  Duplicates: 0  Warnings: 0

STEP 3 - show tables:

SHOW TABLES

It will show:

+--------------------+
| Tables_in_sitename |
+--------------------+
| users              |
+--------------------+
1 row in set (0.001 sec)

Step 4 - Show columns from the table called users:

SHOW COLUMNS FROM users;

It will show:

+-------------------+-----------------------+------+-----+---------+----------------+
| Field             | Type                  | Null | Key | Default | Extra          |
+-------------------+-----------------------+------+-----+---------+----------------+
| user_id           | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| first_name        | varchar(20)           | NO   |     | NULL    |                |
| last_name         | varchar(40)           | NO   |     | NULL    |                |
| email             | varchar(60)           | NO   |     | NULL    |                |
| pass              | char(128)             | NO   |     | NULL    |                |
| registration_date | datetime              | NO   |     | NULL    |                |
+-------------------+-----------------------+------+-----+---------+----------------+
6 rows in set (0.001 sec)

Now everything is alright an I can continue from page 135 in the book.

I only tested this on Apple MacbookAir macOS Big Sur 11.4

Here is all the technical facts:

XAMPP for Linux 7.4.20

includes: Apache 2.4.48, MariaDB 10.4.19, PHP 7.4.20 + SQLite 2.8.17/3.35.5 + multibyte (mbstring) support, Perl 5.32.1, ProFTPD 1.3.6, phpMyAdmin 5.1.1, OpenSSL 1.1.1k, GD 2.2.5, Freetype2 2.4.8, libpng 1.6.37, gdbm 1.8.3, zlib 1.2.11, expat 2.0.1, Sablotron 1.0.3, libxml 2.0.1, Ming 0.4.5, Webalizer 2.23-05, pdf class 0.11.7, ncurses 5.9, pdf class 0.11.7, mod_perl 2.0.11, FreeTDS 0.91, gettext 0.19.8.1, IMAP C-Client 2007e, OpenLDAP (client) 2.4.48, mcrypt 2.5.8, mhash 0.9.9.9, cUrl 7.53.1, libxslt 1.1.33, libapreq 2.13, FPDF 1.7, ICU4C Library 66.1, APR 1.5.2, APR-utils 1.5.4

mysqladmin --version

mysql --version

root@debian:~# mysqladmin --version
mysqladmin  Ver 9.1 Distrib 10.4.19-MariaDB, for Linux on x86_64
root@debian:~# mysql --version
mysql  Ver 15.1 Distrib 10.4.19-MariaDB, for Linux (x86_64) using readline 5.1

solution