fastread homefastrread library fastread menu

MariaDB : Sequences (AUTO_INCREMENT)

Tutorial by:      Date: 2016-04-18 05:50:32

❰ Previous Next ❱

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.

 

❰ Previous Next ❱


MariaDB

Submit Your Thought, Tutorial, Articls etc.

Submit Your Information India's Number one online promotion website