If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.
Here is example of auto increment field Im looking for auto time stamp
create table jamielib/customer
(cust# smallint not null
generated always as identity
(start with 1
increment by 1
shipped_to varchar (5),
name varchar (50),
address varchar (40),
state char (2),
Primary key (Cust#))
a bit of IBM talk
Every time that a new row is added to a table with an identity
column, the identity column value in the new row is incremented
(or decremented) by the system. Only columns of type SMALLINT,
INTEGER, BIGINT, DECIMAL, or NUMERIC can be created as identity
columns. You are allowed only one identity column per table.
When you are changing a table definition, only a column that
you are adding can be specified as an identity column;
existing columns cannot.
When you create a table, you can define a column in the table
to be an identity column. For example, create a table ORDERS
with 3 columns called ORDERNO, SHIPPED_TO, and ORDER_DATE.
Define ORDERNO as an identity column.
CREATE TABLE ORDERS
(ORDERNO SMALLINT NOT NULL
GENERATED ALWAYS AS IDENTITY
(START WITH 500
INCREMENT BY 1
SHIPPED_TO VARCHAR (36) ,
This column is defined with starting value of 500,
incremented by 1 for every new row inserted, and will
recycle when the maximum value is reached. In this example,
the maximum value for the identity column is the maximum value
for the data type. Because the data type is defined as SMALLINT,
the range of values that can be assigned to
ORDERNO is from 500 to 32767. When this column value reaches 32767,
it will restart at 500 again. If 500 is still assigned to a
column, and a unique key is specified on the identity column,
then a duplicate key error is returned. The next insert will
attempt to use 501. If you do not have a unique key specified
for the identity column, 500 is used again, regardless of how
many times it appears in the table.
For a larger range of values, you could specify the column to be
an INTEGER or even a BIGINT. If you wanted the value of the
identity column to decrease, you would specify a negative
value for the INCREMENT option. It is also possible to
specify the exact range of numbers by using MINVALUE and MAXVALUE.
You can modify the attributes of an existing identity column
using the ALTER TABLE statement. For example, if you wanted to
restart the identity column with a new value:
ALTER TABLE ORDER
ALTER COLUMN ORDERNO
RESTART WITH 1
You can also drop the identity attribute from a column:
ALTER TABLE ORDER
ALTER COLUMN ORDERNO
The column ORDERNO remains as a SMALLINT column, but the identity
attribute is dropped. The system will no longer generate
values for this column.
--also-- Date is filled with current date!
Using ROWID is another way to have the system assign a unique value
to a column in a table. ROWID is similar to indentity columns, but
rather than being an attribute of a numeric column, it is a separate
data type. To create a table similar to the identity column example:
You can try using a trigger on the table that updates a timestamp field with the current_timestamp whenever an insert occurrs.
1. There is no guarantee that an timestamp value will be really unique! The faster the machines are, the greater is the chance, that several rows get written in the same microsecond. New technologies, like Concurrent Write Support, where first the adresses for the new requested rows are returned before writing physically will force paralell writes.
2. The implementation of a trigger program is always a dynamic call. That means a lot of overhead. Using an identity column or a ROWID column instead will not cause this overhead, and it gets performed on the database level (on almost the lowest level).
For performance issues identity columns or rowid should be prefered.