Neoview SQL Reference Manual (R2.4)

Considerations for ALTER TABLE
Effect of Adding a Column on View Definitions
The addition of a column to a table has no effect on existing view definitions. Implicit column
references specified by SELECT * in view definitions are replaced by explicit column references
when the definition clauses are originally evaluated.
Authorization and Availability Requirements
To alter a table, you must own the table, be the schema owner, or be granted the ALTER or
ALTER_TABLE privilege.
ALTER TABLE works only on user-created tables.
Altering the MAXVALUE and INCREMENT BY options on IDENTITY Columns
The following rules apply for altering IDENTITY columns:
• You can alter the MAXVALUE or INCREMENT BY options only on IDENTITY columns.
• The only options you can alter for an IDENTITY column are MAXVALUE or INCREMENT
BY.
• Only one option, MAXVALUE or INCREMENT BY, can be altered at a time.
With regard to the INCREMENT BY option, the INCREMENT BY option cannot be 0 (zero), less
than 0 (zero), and cannot be greater than the maximum value of the data type of the IDENTITY
column.
With regard to the MAXVALUE option, a valid numeric value must be specified for the
MAXVALUE option. The MAXVALUE option cannot be 0 (zero), less than 0 (zero), and cannot
be greater than the maximum value of the data type of the IDENTITY column. In addition, the
MAXVALUE option must be greater than the current value of the IDENTITY column. The
MAXVALUE option cannot be less than the INCREMENT BY option.
Altering the MAXVALUE Setting on IDENTITY Columns
When the MAXVALUE maximum is reached for the IDENTITY column, an error is raised that
says the maximum has been exceeded. Values will not be restarted. If an artificially low cycle
range was created by having the MAXVALUE set to a value lower than the natural maximum
allowed for the data type, the ALTER TABLE ALTER COLUMN SET MAXVALUE option can
be used to raise the maximum, including up to the natural maximum allowed for the data type.
This allows for more available values in the cycle range for the internal sequence generator.
Inserts will be successful until the new maxvalue is reached.
NO MAXVALUE is not allowed. If you use NO MAXVALUE on the ALTER TABLE ALTER
COLUMN specification, an error will be raised:
>>alter table T115T002 alter column surrogate_key set NO MAXVALUE;
*** ERROR[1595] The MAXVALUE option for the IDENTITY column SURROGATE_KEY
must be a valid numeric value. NO MAXVALUE is not allowed.
--- SQL operation failed with errors.
Rules for ALTER TABLE ALTER COLUMN
The only options that can be altered for an IDENTITY column are:
• INCREMENT BY
• MAXVALUE
These rules apply:
ALTER TABLE Statement 49