MariaDB: Sequences (AUTO_INCREMENT)
This MariaDB tutorial explains how to create sequences using the AUTO_INCREMENT attribute in MariaDB with syntax and examples.
Description
In MariaDB, you can create a column that contains a sequence of numbers (1, 2, 3, and so on) by using the AUTO_INCREMENT attribute. The AUTO_INCREMENT attribute is used when you need to create a unique number to act as a primary key in a table.
Syntax
The syntax to create a sequence (or use the AUTO_INCREMENT attribute) in MariaDB is:
CREATE TABLE table_name ( column1 datatype NOT NULL AUTO_INCREMENT, column2 datatype [ NULL | NOT NULL ], ... );
- AUTO_INCREMENT
- The attribute to use when you want MariaDB to assign a sequence of numbers automatically to a field (in essence, creating an autonumber field).
- NULL or NOT NULL
- Each column should be defined as NULL or NOT NULL. If this parameter is omitted, the database assumes NULL as the default.
Note
- You can use the LAST_INSERT_ID function to find last value assigned by the AUTO_INCREMENT field.
Example
Let's look at an example of how to use a sequence or the AUTO_INCREMENT attribute in MariaDB.
For example:
CREATE TABLE sites ( site_id INT(11) NOT NULL AUTO_INCREMENT, site_name VARCHAR(30) NOT NULL, host_name VARCHAR(40), creation_date DATE, CONSTRAINT sites_pk PRIMARY KEY (site_id) );
This MariaDB AUTO_INCREMENT example creates a table called sites which has 4 columns and one primary key:
- The first column is called site_id which is created as an INT datatype (maximum 11 digits in length) and can not contain NULL values. It is set as an AUTO_INCREMENT field which means that it is an autonumber field (starting at 1, and incrementing by 1, unless otherwise specified.)
- The second column is called site_name which is a VARCHAR datatype (maximum 30 characters in length) and can not contain NULL values.
- The third column is called host_name which is a VARCHAR datatype (maximum 40 characters in length) and can contain NULL values.
- The fourth column is called creation_date which is a DATE datatype and can contain NULL values.
- The primary key is called sites_pk and is set to the site_id column.
Set AUTO_INCREMENT starting value
Now that you've created a table using the AUTO_INCREMENT attribute, how can you change the starting value for the AUTO_INCREMENT field if you don't want to start at 1?
You can use the ALTER TABLE statement to change or set the next value assigned by the AUTO_INCREMENT.
Syntax
In MariaDB, the syntax to change the starting value for an AUTO_INCREMENT column using the ALTER TABLE statement is:
ALTER TABLE table_name AUTO_INCREMENT = start_value;
- table_name
- The name of the table whose AUTO_INCREMENT value you wish to change. Since a table in MariaDB can only contain one AUTO_INCREMENT column, you are only required to specify the table name that contains the sequence. You do not need to specify the name of the column that contains the AUTO_INCREMENT value.
- start_value
- The next value in the sequence to assign in the AUTO_INCREMENT column.
Example
Let's look at an example of how to change the starting value for the AUTO_INCREMENT column in a table in MariaDB.
For example:
ALTER TABLE sites AUTO_INCREMENT = 1000;
This MariaDB AUTO_INCREMENT example would change the next value in the AUTO_INCREMENT field (ie: next value in the sequence) to 1000 for the site_id field in the sites table.
Other Related Tutorials
Here are some other tutorials to help you learn more about sequences in MariaDB:
Advertisements