Oracle Generated Always As Identity Primary Key
Use the ALTER TABLE...MODIFY clause to change one or more attributes of a table's IDENTITY column and its Sequence Generator (SG) options.
Each IDENTITY column is generated in one of the following ways:
Create table fiprmaster ( fiprid int generated always as identity (start with 100 increment by 1 minvalue 100 nomaxvalue nocycle nocache order) primary key, fiprname varchar2(40) not null, buname varchar2(40) not null, location varchar2(40) not null, recordsourcename varchar2(200) not null, priorityqueue varchar2(40) not null, status varchar2(40) not null, indexingtype varchar2(40) not. Script Name Defaulting timestamp and primary key values; Description Using default and default on null to reduce the need to code triggers and sequences. Avoid need to create sequences by using identity datatype, avoid need to default creation dates via a trigger by using the default on null.
| IDENTITY Column Statement | Description |
|---|---|
GENERATED ALWAYS AS IDENTITY | The sequence generator always supplies an IDENTITY value. You cannot specify a value for the column. |
GENERATED BY DEFAULT AS IDENTITY | The sequence generator supplies an IDENTITY value any time you do not supply a column value. |
GENERATED BY DEFAULT ON NULL AS IDENTITY | The sequence generator supplies the next IDENTITY value if you specify a NULL columnn value. |
This section presents ways to change or drop an IDENTITY column from a table. The ALTER TABLE statement lets you add, remove, or alter a field in any table definition. Use the ALTER TABLE statement to modify an IDENTITY field.
Note:
TheMODIFY clause in an ALTER TABLE... statement is supported only on IDENTITY columns. The next example adds an IDENTITY field to a new table, test_alter, created without an IDENTITY. The example also specifies several attributes for the associated SG for test_alter:
To remove the IDENTITY column, so no such field remains, use ALTER TABLE with a DROP id clause:
To keep the id column, but remove its IDENTITY definition, use ALTER TABLE with a MODIFY id DROP IDENTITY clause:
You can change the SG attributes. The new values take effect on subsequent client calls to access the SG attributes. For example, this happens when the cache has no more values, or when the attributes stored at the client have timed out.
To change the basic property of an IDENTITY column being GENERATED ALWAYS to GENERATED BY DEFAULT, see the next ALTER TABLE example. The example also shows how to change the SG attributes from their original definitions, START WITH, INCREMENT BY, MAXVALUE, CACHE and CYCLE.
Note:
The client has a time-based cache to store the SG Attributes. The client connects to the server to refresh this cache after it expires. The default timeout is 5 minutes. Change this default by settingDb2 Generated Always As Identity
sgAttrsCacheTimeout