Neoview SQL Reference Manual (R2.5)

identity-column-specification
indicates that a particular column is a sequence-generating IDENTITY column. GENERATED
BY DEFAULT AS IDENTITY indicates that the system will generate values for this column
by default or values can be specified by the user. GENERATED ALWAYS AS IDENTITY
indicates that the system will always generate values for this column by default. User-specified
values are not allowed with GENERATED ALWAYS AS IDENTITY.
Examples of DEFAULT
• This example uses DEFAULT clauses on CREATE TABLE to specify default column values:
CREATE TABLE items
( item_id CHAR(12) NO DEFAULT
,description CHAR(50) DEFAULT NULL
,num_on_hand INTEGER DEFAULT 0 NOT NULL;
• This example uses DEFAULT clauses on CREATE TABLE to specify default column values:
CREATE TABLE persnl.project
( projcode NUMERIC (4) UNSIGNED
NO DEFAULT
NOT NULL
,empnum NUMERIC (4) UNSIGNED
NO DEFAULT
NOT NULL
,projdesc VARCHAR (18)
DEFAULT NULL
,start_date DATE
DEFAULT CURRENT_DATE
,ship_timestamp TIMESTAMP
DEFAULT CURRENT_TIMESTAMP
,est_complete INTERVAL DAY
DEFAULT INTERVAL '30' DAY
,PRIMARY KEY (projcode) ;
• This example uses identity-column-specification on CREATE TABLE to specify
that the system generates the value for column surrogate_key if you have not specified
one:
CREATE TABLE T (surrogate_key LARGEINT GENERATED BY
DEFAULT AS IDENTITY NOT NULL,
name CHAR (256) NOT NULL,
order_number INT UNSIGNED NOT NULL,
primary key(surrogate_key, order_number)
)
HASH PARTITION BY (surrogate_key);
DEFAULT Clause 315