MySQL auto_increment Clause

This post looks slightly different to earlier ones as I created it with the verbose option and ran the SQL from a file using the source command. MySQL allows you to increment column values automatically with the auto_increment clause. You can only auto increment one column per table and that column must be used as a key:

 

--------------

create table andrews_table(

id int(4) auto_increment,

location varchar(10))

--------------

 

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

--------------

create table andrews_table(

id int(4) auto_increment,

location varchar(10),

primary key(id))

--------------

 

Query OK, 0 rows affected (0.00 sec)

 

When you insert rows into the table, you do not need to supply values for the column which is automatically incremented. Note how three rows are being inserted simultaneously:

 

--------------

insert into andrews_table (location)

values ('Redhill'), ('Crawley'), ('Horsham')

--------------

 

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0

 

By default, it looks as if the auto incremented column starts at 1 and goes up by 1 each time. I will look at this in more detail in future posts:

 

--------------

select * from andrews_table

--------------

 

+----+----------+

| id | location |

+----+----------+

|  1 | Redhill  |

|  2 | Crawley  |

|  3 | Horsham  |

+----+----------+

3 rows in set (0.01 sec)

 

MySQL allows you to look at the value automatically inserted into the last row. For a multi-row insert, this returns the value inserted into the first row created by that insert:

 

--------------

select last_insert_id()

--------------

 

+------------------+

| last_insert_id() |

+------------------+

|                1 |

+------------------+

1 row in set (0.00 sec)

 

--------------

insert into andrews_table (location)

values ('Croydon')

--------------

 

Query OK, 1 row affected (0.00 sec)

 

--------------

select * from andrews_table

--------------

 

+----+----------+

| id | location |

+----+----------+

|  1 | Redhill  |

|  2 | Crawley  |

|  3 | Horsham  |

|  4 | Croydon  |

+----+----------+

4 rows in set (0.00 sec)

 

--------------

select last_insert_id()

--------------

 

+------------------+

| last_insert_id() |

+------------------+

|                4 |

+------------------+

1 row in set (0.00 sec)

 

mysql>

How to Create a MySQL Table with a Primary Key

This is one way to create a table with a primary key. In this example, the primary key consists of one column. In a future post, I will show how to set up a primary key with more than one column: 

mysql> create table andrews_table

    -> (col1 varchar(10), primary key(col1));

Query OK, 0 rows affected (0.02 sec)

 

mysql> insert into andrews_table values ('Fred');

Query OK, 1 row affected (0.00 sec)

mysql>
  

The purpose of a primary key is to ensure that the values in the primary key column remain unique. So, if you try to insert Fred into the primary key column again, you would expect the insert to fail:

 

mysql> insert into andrews_table values ('Fred');

ERROR 1062 (23000): Duplicate entry 'Fred' for key 1

mysql> select * from andrews_table;

+------+

| col1 |

+------+

| Fred |

+------+

1 row in set (0.00 sec)

 

mysql>