May 11, 2015 Generated Key Column option will not generate any kind of new column (nor any other option in TC) and it is an optional field. You only need to give it a key column if the set of columns in 'Input primary key columns' might compare to more than one row at the comparison table. The next example adds an IDENTITY field to a new table, testalter, created without an IDENTITY.The example also specifies several attributes for the associated SG for testalter:. CREATE Table Testalter (id INTEGER, name STRING, PRIMARY KEY (id)); ALTER Table Testalter (ADD newid INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 2 MAXVALUE 100 CACHE 10. If you have employee data that includes a social security number as a primary key and multiple entries for some of these keys, specify a column in the comparison table with unique keys as the generated key column. A generated key column indicates which row is used in the comparison of a set that contains identical primary keys.
- Generated Key Column In Table Comparison Worksheet
- Generated Key Column In Table Comparison Calculator
- Waters Column Comparison
Use the
ALTER TABLE...MODIFY
clause to change one or more attributes of a table's IDENTITY column and its Sequence Generator (SG) options. Generated Key Column In Table Comparison Worksheet
Each IDENTITY column is generated in one of the following ways:
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:
The MODIFY
clause in an ALTER TABLE...
Generated Key Column In Table Comparison Calculator
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: Waters Column Comparison
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 setting sgAttrsCacheTimeout
in KVStoreConfig.