SQL Standard

SQL Sequence

Have you ever used the auto-increment or serial statement in a database? If so, then you have an SQL sequence. Although Sequences and identity columns are slightly different, they share common characteristics.

A sequence refers to a set of unique integer values that are generated on demand. They are a prevalent feature in many database engines. This is because various scenarios require each record in the database to have a unique identifier.

Let us discuss the concept of SQL sequences in this tutorial.

How SQL Sequences Work

There is no universal way to describe how sequences work. This is because various database engines implement them differently.

Let us learn how this works.

Sequences in MySQL

Sequences in MySQL are implemented by setting the AUTO_INCREMENT attribute in a column. In most cases, this column is also selected as the primary key as each value in the column must be unique.

We can illustrate how to use a sequence in MySQL as shown in the query below:

-- MySQL

create table table_name(

column_name int auto_increment primary key,

column_name2 varchar(100),

);

The above query creates a column with an SQL sequence using the auto_increment keyword. Note that we also set the primary key constraint to the column.

Although adding sequences in MySQL is as easy as illustrated above, there are a few points to note:

  1. The sequence column in MySQL must be indexed. This means setting the column as primary key or unique.
  2. The column cannot contain null values. You can solve this by setting the NOT NULL constraint to the column. In some cases, MySQL may explicitly specify the NOT NULL constraint when the auto_increment constraint is added.
  3. The values in the sequence column start from 1 and increase by 1 for each record added to the table.
  4. MyISAM and InnoDB do not support the reuse of deleted sequence numbers. Hence, MySQL will assign the following value to the next inserted sequence if you remove a record. For example, if you delete 10, MySQL will not reuse that value. Instead, it will assign 11 to the next record.
  5. MySQL does allow you to reset the auto-increment value.

Sequence in PostgreSQL

In PostgreSQL, sequences are user-defined objects bound to the schema in which they are created.

To create a sequence in PostgreSQL, use the syntax as shown below:

-- PostgreSQL

create sequence [if not exists] sequence_name

as {data_type}

increment by [increment_value]

minvalue [minumum_value] | no minumum_value

maxvalue [maximum_value] | no maximum_value

start [with] start

cache

no cycle

owned by [table_name.column_name] none

Okay, what does that all mean? Allow us to break it down into smaller sections that we can understand.

CREATE SEQUENCE

The first part is the create sequence. This allows you to create a new sequence if it does not exists. The sequence name can be anything you wish.

AS data_type

The AS clause allows you to specify the data type for the sequence. PostgreSQL allows SMALLINT, INT, and BIGINT types. If you do not wish to identify the data type, you can skip this clause, and PostgreSQL will default to BIGINT.

INCREMENT BY

This clause specifies the value that is added to the current sequence to create a new sequence value. For example, if the sequence is {1,2,3,4,5}, the increment value is 1.

If you wish to start from the top and descend as the records are added, specify a negative value to the increment by clause.

By default, PostgreSQL will use the increment value of 1.

MINVALUE

This clause defines the minimum value for the sequence. If no value is specified, PostgreSQL will use the default.

MAXVALUE

The maxvalue clause sets the maximum value for the sequence.

STARTS WITH

This clause sets the start value for the sequence. If no value is specified, the minvalue is used for ascending sequences, and maxvule is used for descending sequences.

CACHE

This clause will define how many sequence numbers are to be preallocated and stored in memory. This allows for faster access. By default, PostgreSQL will use a value of 1 hence no-cache.

CYCLE

The CYCLE clause allows you to specify the wrap around when the max or min values are attained in the sequence. PostgreSQL will use min and maxvalue respectively when the limit is reached if the cycle not specified.

OWNED BY

This option specifies with which the table column the sequence is associated. Hence, if the column or table is dropped, the sequence is deleted as well.

If the sequence is not associated with any table or column, you can set the OWNED BY to NONE.

To create a simple sequence in PostgreSQL, run the query:

create sequence my_seq

increment by 1

minvalue 1

maxvalue 1000

start 1

owned by my_table.my_column

The above query creates a new sequence associated with my_column in the my_table table.

You can explore more about PostgreSQL sequences in the resource below:

https://www.postgresql.org/docs/current/sql-createsequence.html

Sequences in SQL Server

Like PostgreSQL, sequences in SQL Server are user-defined objects. Therefore, they use closely similar syntax for sequences in PostgreSQL. The only difference is the syntax.

The example syntax is as illustrated below:

CREATE SEQUENCE [schema_name.] sequence_name

[ AS integer_type ]

[ START WITH start_value ]

[ INCREMENT BY increment_value ]

[ { MINVALUE [ min_value ] } | { NO MINVALUE } ]

[ { MAXVALUE [ max_value ] } | { NO MAXVALUE } ]

[ CYCLE | { NO CYCLE } ]

[ { CACHE [ cache_size ] } | { NO CACHE } ];

Instead of NONE, SQL Server uses NO CYCLE and NO CACHE.

You can explore more about SQL Server sequences in the resource below:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver15

NOTE: For Oracle database users, check the resource below

https://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_6014.htm

Conclusion

This detailed article covered SQL Sequences and how we can use them in our databases. Stay tuned for more tutorials.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list