Number Generator for a Single Table
Column 
You can use the DEFAULT specification SERIAL(n) to create a number generator (automatically generated sequence of numbers) for a table column.
This is commonly used for generating a primary key, for example.
Prerequisites
You require the demo data for the SQL Tutorial.
Start the query tool SQL Studio as database administrator MONA with password RED and log on to the demo database instance DEMODB.
Creating a Number Generator
...
Create the pers3 table with the DEFAULT specification SERIAL(n).
If you use the keyword SERIAL without specifying a number, the default value for the start of the number generator is 1. If you want to start the number generator with the number n, you have to enter the number n explicitly after the keyword SERIAL: SERIAL(n).
CREATE TABLE hotel.pers3
(pno FIXED(10) DEFAULT SERIAL PRIMARY KEY,
name CHAR (20))
or
CREATE TABLE hotel.pers3
(pno SERIAL PRIMARY KEY,
name CHAR (20))
As the data type SERIAL is displayed in the form of FIXED(10) DEFAULT SERIAL, both CREATE TABLE statements return the same definition for the pers3 table. The first value generated by the number generator is the number 1.
See also:
SQL Reference Manual, 
DEFAULT Specification
(default_spec)
SQL Reference Manual, Data Type
(data_type)
Using the Number Generator
You can use the number generator in the pno column in your application.
You do not need to enter a value for the SERIAL column in INSERT statements; this is generated by the number generator.
The first value generated by the number generator in the example above is a 1. The value in the SERIAL column increases by 1 with each additional insertion of a value into the table.
INSERT hotel.pers3 (name) VALUES
('Baker')
//
INSERT hotel.pers3 (name) VALUES ('Miller')
//
SELECT * FROM hotel.pers3
Result
PNO |
NAME |
1 |
Baker |
2 |
Miller |
A column with the DEFAULT specification SERIAL cannot be changed.
A column with the DEFAULT specification SERIAL can also be a foreign key column. Such columns are actually ideally suited to this purpose as their values cannot be changed and are only used in the application for the purposes specified in the application logic applied to the column.
Displaying the Current Value of the Number Generator
You can use the DUAL table, which is automatically generated by the database system, to display the current value of the number generator. The DUAL table can be used by all database users. As the DUAL table has only one row, you can use it to display the value of the number generator using a SELECT statement.
You can query the current value of the number generator using <table_name>.CURRVAL where <table_name> is the name of the table that contains the DEFAULT specification SERIAL.
SELECT hotel.pers3.CURRVAL FROM dual
Result
|
EXPRESSION1 |
1 |
2 |
See also:
More examples for Data Definition