USING SEQUENCES



MySQL - Using Sequences


A sequence is a set of integers 1, 2, 3, ... that are generated in order on a specific demand. Sequences are frequently used in the databases because many applications require each row in a table to contain a unique value and sequences provide an easy way to generate them.

This chapter describes how to use sequences in MySQL.


Using AUTO_INCREMENT Column :


In MySQL, 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 MySQL 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 MySQL 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 MySQL.

CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25),
  birthday DATE,
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

This MySQL AUTO_INCREMENT example creates a table called contacts which has 4 columns and one primary key:


  • The first column is called contact_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 last_name which is a VARCHAR datatype (maximum 30 characters in length) and can not contain NULL values.

  • The third column is called first_name which is a VARCHAR datatype (maximum 25 characters in length) and can contain NULL values.

  • The fourth column is called birthday which is a DATE datatype and can contain NULL values.

  • The primary key is called contacts_pk and is set to the contact_id column.


    Set AUTO_INCREMENT starting value :

    ALTER TABLE table_name AUTO_INCREMENT = start_value;
    

    EXAMPLE :

    ALTER TABLE contacts AUTO_INCREMENT = 50;
    

    This MySQL AUTO_INCREMENT example would change the next value in the AUTO_INCREMENT field (ie: next value in the sequence) to 50 for the contact_id field in the contacts table.


    Renumbering an Existing Sequence :


    There may be a case when you have deleted many records from a table and you want to re-sequence all the records. This can be done by using a simple trick, but you should be very careful to do so if your table is having joins with the other table

    If you determine that the resequencing of an AUTO_INCREMENT column is unavoidable, the way to do it is to drop the column from the table, then add it again.


    mysql> ALTER TABLE insect DROP id;
    mysql> ALTER TABLE insect
       -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
       -> ADD PRIMARY KEY (id);
    

    Starting a Sequence at a Particular Value :


    By default, MySQL will start sequence from 1, but you can specify any other number as well at the time of the table creation.

    The following program is an example which shows how MySQL will start the sequence from 100.

     CREATE TABLE insect
       -> (
       -> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,
       -> PRIMARY KEY (id),
       -> name VARCHAR(30) NOT NULL, # type of insect
       -> date DATE NOT NULL, # date collected
       -> origin VARCHAR(30) NOT NULL # where collected
    );
    

    Alternatively, you can create the table and then set the initial sequence value with the ALTER TABLE command.



    MySQL TRIGGERS

    MySQL - Triggers

    posted on 2019-11-29 21:44:07 - mysql Tutorials


    Grant_ Revoke Privilege

    MySQL - Grant_ Revoke Privilege

    posted on 2019-11-26 23:15:04 - mysql Tutorials


    MySQL Vs SQL

    MySQL Vs SQL

    posted on 2019-11-25 05:02:26 - mysql Tutorials


    What Is Oracle

    What Is Oracle

    posted on 2019-11-29 01:11:21 - ORACLE Tutorials


    TRIGGER

    ORACLE - Trigger

    posted on 2019-11-28 22:14:22 - ORACLE Tutorials


    CURSOR

    ORACLE - Cursor

    posted on 2019-11-28 22:13:54 - ORACLE Tutorials