Altering Tables - Learning MySQL and MariaDB - page 85

This particular usage of the DROP keyword doesn't deletedata in the columns. It just al-
ters the column settings so there is no default value. Run the SHOW COLUMNS statement
again on your computer to see that the default has been reset. Then put the defaultback to
7 .
Setting the Value of AUTO_INCREMENT
Many of themain tables in a database will have a primary key that uses the
AUTO_INCREMENT option. That creates an AUTO_INCREMENT variable in the table
called tables in the information_schema database. You may recognize that data-
base name. We saw the information_schema database in the results of the SHOW
DATABASE statement in Starting to Explore Databases . When you create a table, MySQL
adds a row to the table called tables in the information_schema database. One of
the columns of that table is called auto_increment . That is where you can find the
value of the next row to be created in a table. This is initially set to a value of 1, unless
you set it to a different number when creating the table. Let's run a SELECT statement to
get that value from the information_schema database, from the tables table:
SELECT auto_increment
FROM information_schema.tables
WHERE table_name = 'birds';
+----------------+
| auto_increment |
+----------------+
| 7 |
+----------------+
Because we entered data for only six birds in the birds table, and the value of
AUTO_INCREMENT was not set when the table was created, it started at 1 and now has a
value of 7. That means the next row we add to the table will have 7 in the column.
If you would like to change the value of AUTO_INCREMENT for a particular table, you
can do so with the ALTER TABLE statement. Let's set the value of AUTO_INCREMENT
for the birds table to 10, just to see how to change it this way. While we're at it, let's
switch thedefault database back to rookery . Enter the following in mysql :
USE rookery
ALTER TABLE birds
AUTO_INCREMENT = 10 ;