SAP DB is now SAP MaxDB! For more information, visit the SAP MaxDB pages in the SAP Developer Network.

SERIAL Data Type

Use

In addition to sequences that are explicitly generated and must be explicitly specified in INSERT statements, for example, there is also the data type SERIAL for columns, which represents the short form for FIXED(10) DEFAULT SERIAL.

The specification DEFAULT SERIAL is comparable to the attribute AUTO_INCREMENT in MYSQL.

The following rules apply for the SAP DB database system:

1. Within a table, there can only be one column with a DEFAULT SERIAL attribute.
2. In the case of an INSERT statement, do not specify a value for the column that has the attribute DEFAULT SERIAL. Here, the next value generated by the implicitly generated number generator is entered as the system default value. The first entered value is 1. With each INSERT it is increased by a value of 1.
   The effect is therefore the same as the generation of a sequence <sequence_name> with INCREMENT 1 and specification of <sequence_name>.NEXTVAL for the relevant column of the INSERT statement.
3. A column with the attribute DEFAULT SERIAL cannot be changed.
4. The current DEFAULT SERIAL value can be requested using <table_name>.CURRVAL where <table_name> is the name of the table that contains the column defined as DEFAULT SERIAL.
   For the number generator generated by DEFAULT SERIAL, this is an equivalent to the @@IDENTITY or LAST_INSERT_ID() functionality of other database systems. It implies that the data type SERIAL is also suitable for table columns that have FOREIGN KEY relationships with other tables. It is easier to use, and because the value cannot be changed, it is also more secure. In addition, it is guaranteed that the generated values are only used for this one purpose, which is fixed in the application logic via the table column. This may be relevant for the assignment of order numbers, for example.

Example: Use of DEFAULT SERIAL

This site is sponsored by SAP AG.