0080. MonetDB – Serial Data Type and Sequences

Serial Data Type

In a table, synthetic (surrogate) key is a column which have a unique value for each row, but its content is not derived from application data, but is generated. If none of the rows is deleted from this table, then this column will usually have numbers as a sequence 1,2,3,4…

There are many advantages in using surrogate keys but this time we will talk only about that how to create them in MonetDB. We can use code like this:

CREATE TABLE SerialTable ( SerialCol SERIAL, Name VARCHAR(10)  )

When we enter data in this column, we do not specify values for SerialCol. Those values will be automatically created because this column is of SERIAL data type.

INSERT INTO SerialTable (Name) VALUES ('Obert')

When we read from this table, this would be result. Number one will be automatically added to SerialCol.

If we repeat this INSERT statement several time, each time we would get next number.

This means that, for this column, there is a database object that is remembering last used number, so that next time it can provide next number in a sequence. We can find that database object in sys.sequences system table. Although this table claims that maxvalue is some huge number ( upper limit for BIGINT, or 9223372036854775807  ), this column will actually be of the type INTEGER. Starting value is one, and increment is also one. That is why we are getting numbers 1,2,3…

It is also possible to use BIGSERIAL instead of the SERIAL. In this case our column would be really of the type BIGINT.

If we want to define what kind of integer to use then we are using syntax like this. Instead of INT we can use any other integer data type like TINYINT, SMALLINT, BIGINT, HUGEINT.

CREATE TABLE AutoIncrementTable ( AutoNumberCol INT AUTO_INCREMENT, Name VARCHAR(10) )

In this case sys.sequences table will still show that maxvalue is upper limit for BIGINT, but the real limit will be for INTEGER data type. If we try to enter a value for AutoNumberCol by ourselves, that activity will fail if the number is bigger than 2.7 billion which is upper limit for INTEGER data type.

Maxvalue is always set to BIGINT upper limit because internally MonetDB is storing serial numbers as BIGINT. This doesn't mean that you can not use HUGEINT as AUTO_INCREMENT, you can.

We can generate a new value for our sequence with this statement. "seq_7697" is the name of our sequence. This will increment our sequence by one, so this statement should be used to get next number and then to use it for something. This is usually useful when we want to create new value, and then to use it both in primary key column and foreign key column.

When we are using SERIAL or BIGSERIAL data type, that column will get PRIMARY KEY and NOT NULL constraints.

Always and By Default

Whenever we use SERIAL, BIGSERIAL, we still have ability to enter sequence values by ourselves. Sequence will not be aware of the number we have entered (1) by hand. If sequence try to use the same number (2) that we already entered by hand, it will raise primary key exception.

This is not true if we use AUTO_INCREMENT syntax. In that case we don't have primary key constraint, so it will not be a problem if we enter two same values in our AUTO_INCREMENT column.

If we want to disable ability to enter our own value in sequence column, then we could use this generic syntax.
THIS SYNTAX WILL NOT WORK IN MONETDB. It will create a new sequence, but it will not prevent us from entering our own values.

CREATE TABLE AutoGeneratedTable ( AutoGeneratedCol INT GENERATED ALWAYS AS IDENTITY, Name VARCHAR(10) )

This web page explains that this should not be allowed, but in MonetDB it doesn't work as it should.

[Err] ERROR:  cannot insert into column "rank_id"
DETAIL:  Column "rank_id" is an identity column defined as GENERATED ALWAYS.
https://www.sqltutorial.org/sql-identity/ 

Sequence parameters

Although previous syntax is not prohibiting from entering our own values, that syntax will allows us to use sequence parameters.

CREATE TABLE AutoGeneratedTable ( AutoGeneratedCol INT GENERATED ALWAYS AS IDENTITY ( INCREMENT BY 2 MINVALUE 7 MAXVALUE 10 CACHE 8 CYCLE ), Name VARCHAR(10) )

The parameters (1), allow us to control what will be the content of sys.sequences table (2). As we can see, now the first sequence number will be 7 and next sequence number will be 9 (3).

We can use these parameters:

• START WITH number– This is the starting number in sequence. It could be negative or positive. If this parameter is not declared, then the default value is MINVALUE for ascending sequences and MAXVALUE for descending sequences. START WITH parameter must be bigger than MINVALUE parameter.
• INCREMENT BY number – This way we define difference between two consecutive numbers in a sequence. Default value is 1. If "number" is positive then we have ascending sequence. For negative "number", sequence will be descending.
• NO MINVALUE – NO MIN VALUE means that we are using default value. Default value for ascending sequences is either 1 or START WITH parameter. Default value for descending sequences is minimum value for data type that is associated with the sequence.
• MINVALUE number – Can be negative or positive. Must be smaller than MAXVALUE.
• NO MAXVALUE – This means that we are using the default value. For ascending sequences that value will be the maximum value of the data type that is associated with the sequence. For descending sequences, it is either -1 or START WITH number.
• MAXVALUE number – Can be positive or negative number. Must be greater than minimum value.
• NO CACHE – System will cache next number to be used. This could create problem because in the case of the system failure, that number will be lost. We can prevent such scenario with NO CACHE.
• CACHE number –In order to increase performance, we can cache several next values for our sequence. Number of cached values will be smaller or equal to the CACHE number. In the case of the system failure, all these values will be lost.
• CYCLE – After ascending sequence reach the end of a range, it will start again from the minimal value for associated data type or from the MINVALUE parameter. After descending sequence reach the start of a range, it will start again from the maximal value for associated data type or from the MAXVALUE parameter.
• NO CYCLE – This will not allow looping. If we reach the value outside of the limits of our range, an error will be raised.

Sequence as an Object

We can create sequence as an object and then we can use that object when we create a new table. We create a new sequence like this. We can define all of the parameters in this statement.

CREATE SEQUENCE "seq_NewSequence" AS SMALLINT START WITH 10 INCREMENT BY 2 MINVALUE 7 MAXVALUE 20 CACHE 8 CYCLE

Now that we have our sequence, we can use it for our new table. We will bind sequence object to our column as a default value.

CREATE TABLE SequenceObject ( SequenceCol SMALLINT DEFAULT NEXT VALUE FOR "seq_NewSequence", name VARCHAR(10) );

If we want to alter our sequence object, then we use this statement. Again, we can use all of the parameters. The only difference is that here instead of "START WITH" we use "RESTART WITH".

ALTER SEQUENCE "seq_NewSequence" RESTART WITH 21 INCREMENT BY 3 MINVALUE 15 MAXVALUE 100 CACHE 10 NO CYCLE

Sequence object can be deleted with this statement. It is only possible to delete sequence object if references to that object are deleted. That means that we have to change DEFAULT value of a columns that are using that sequence object.

DROP SEQUENCE "seq_NewSequence";

Info functions

Two statements below are the same:

SELECT NEXT VALUE FOR seq_NewSequence
SELECT NEXT_VALUE_FOR( 'schema', 'seq_NewSequence' )

Both of them will return number 21, but the next time we call them they will return number 22. They shift the sequence.

If we just want to check what is the next number in a sequence then we can use this function. As we can see, it will always return the same number, sequence will not be shifted after each usage.

SELECT GET_VALUE_FOR('schema', 'seq_NewSequence')