ALTER TABLE

ALTER TABLE — change the definition of a table

Syntax

ALTER TABLE [schema_name.]table_name alter_table_command [ ,alter_table_command ]...

Where alter_table_command can be one or more of the following:

ADD [ COLUMN ] column_definition
ADD [ COLUMN ] ( column_definition [ , column_definition ]... )
ADD [ CONSTRAINT [ constraint_name ] ] table_constraint
ADD PARTITION partition_name VALUES LESS THAN ( literal | MAXVALUE ) [ STORE IN storage_group ] }...
ADD PARTITION partition_name VALUES IN ( literal [ ,literal]... ) [ STORE IN storage_group ] }...

ALTER [ COLUMN ] column_name
    { [ SET DATA ] TYPE data_type |
      COLLATION | COLLATE [ = ] }{DEFAULT |8859-1U} collation_name |
      SET DEFAULT default_expr |
      column_constraint |
      DROP IDENTITY [IF EXISTS]

CHANGE [ COLUMN ] column_name new_column_name data_type column_qualifier
CHANGE [ COLUMN ] column_name UPDATE SEQUENCE RANGE

DISABLE INDEX index_name

DROP [ COLUMN ] column_name
DROP [ COLUMN ] ( column_name [ ,column_name ]... )
DROP CONSTRAINT [ constraint_name ]
DROP PARTITION partition_name

ENABLE INDEX index_name

MODIFY [ COLUMN ] column_definition
MODIFY PARTITION BY RANGE (column_name)
    [STORE IN default_storage_group]
    PARTITION partition_name VALUES LESS THAN (literal | MAXVALUE)
    [STORE IN storage_group]

MOVE PARTITION partition_name
    [STORE IN storage_group]

REBUILD FORMAT

REBUILD { INDEXES [USING {MERGE TREE|BTREE}]| INDEX index_name [USING {MERGE TREE|BTREE}] }

REMOVE RECORD record_id [ PARTITION partition_id ] (1)

RENAME [ COLUMN ] column_name TO new_column_name

RENAME [ INDEX ] index_name TO new_index_name

RENAME TO [ schema_name. ]new_table_name

STORE IN destination_storage_group
1 For internal use only. Use only when instructed by NuoDB Customer Support.

Where column_definition is:

column_name data_type column_qualifier

Where column_qualifier can be one or more of the following:

column_constraint
{ COLLATION | COLLATE [ = ] }{DEFAULT |8859-1U} collation_name
DEFAULT expression
generated_column

and column_constraint is one or more of the following:

NULL | NOT NULL
PRIMARY KEY
REFERENCES reference_table [ ( reference_column [, ... ] ) ]
CHECK ( boolean_expression )
UNIQUE

and generated_column can be one of the following:

GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [(generator_name)]
GENERATED ALWAYS AS (expression) PERSISTED

Where table_constraint can be one of the following:

CHECK ( boolean_expression )
PRIMARY KEY ( column_name [, ...] ) [ index_qualifiers ]
FOREIGN KEY ( column_name [, ...] ) REFERENCES reference_table [ ( reference_column [, ... ] ) ]
UNIQUE ( column_name [, ...] )
KEY indexName ( column_name[, ...]) [ index_qualifiers ]
UNIQUE KEY indexName ( column_name[, ...]) [ index_qualifiers ]
Altering table partitions using ALTER TABLE ADD PARTITION, ALTER TABLE DROP PARTITION, ALTER TABLE MODIFY PARTITION, ALTER TABLE MOVE PARTITION, ALTER TABLE REMOVE RECORD, or ALTER TABLE STORE IN is not a Transactional DDL operation. The action is not reversible using a ROLLBACK statement.

Description

Use ALTER TABLE to change the definition of an existing table.

The ALTER TABLE command is not supported for temporary tables.

Adding or Dropping one or more Columns

To add a column or columns to a table, use:

ADD [ COLUMN ] column_definition
ADD [ COLUMN ] ( column_definition[ , column_definition ]... )

This will add a new column or columns to the specified table. See column_definition above for syntax. ADD COLUMN requires the same syntax as when defining a column in the CREATE TABLE command (See CREATE TABLE).

To add a new column with a FOREIGN KEY reference to a UNIQUE constraint of the referencing table, first use the ADD COLUMN option of the ALTER TABLE command to add the new column. Then, use the ADD CONSTRAINT option of the ALTER TABLE command to add the FOREIGN KEY reference to the UNIQUE constraint of the referencing table.

To drop a column or columns from a table use:

DROP [ COLUMN ] column_name
DROP [ COLUMN ] ( column_name [ , column_name ]... )

This will drop the specified column or columns from the table.

  • A column defined in an index cannot be dropped.

  • A column used in table partitioning cannot be dropped.

  • To drop a column that is referenced in a table constraint, the table constraint must be dropped first.

Adding or Dropping a Table Constraint

A table constraint is specified at the table level. A table constraint can be a check constraint, a uniqueness constraint, a primary key or a foreign key (referential constraint). See table_constraint above for NuoDB supported table constraint syntax. To add a named or unnamed constraint to a table, use:

ADD [ CONSTRAINT [ constraint_name ] ] table_constraint

This syntax will allow you to add a CHECK, UNIQUE, PRIMARY KEY, or FOREIGN KEY constraint. An optional constraint_name may be specified. If a constraint_name is not specified, the default name given to the constraint will be generated. The constraint, including its name, is stored in various system tables, depending on the type of constraint, as follows:

It is useful to name a constraint for two reasons:

  1. The error message produced when the constraint is not met will include the explicit name of the constraint.

  2. You can easily drop the constraint using ALTER TABLE DROP CONSTRAINT constraint_name. See Dropping a Table Constraint, below.

When adding a unique, primary key, or foreign key table constraint, the CONSTRAINT keyword is only necessary if you want to name the table constraint. Otherwise, the syntax can be simplified to ADD UNIQUE…​, ADD PRIMARY KEY…​ or ADD FOREIGN KEY…​.

For a CHECK table constraint, a constraint_name is optional, but the CONSTRAINT keyword is always required.

When adding any constraint, if the data in the column or columns does not satisfy the constraint, an error message will be issued and the constraint will not be added. The exception to this is the FOREIGN KEY constraint where referential integrity is not enforced by NuoDB.

When adding a primary key using either syntax, if the column or columns are not already marked NOT NULL, then ALTER TABLE attempts to set them to NOT NULL. If NULL values exist, an error message is issued and the primary key is not added.

A UNIQUE constraint will allow one or more of the columns defined by the constraint to contain a NULL value. Unless the UNIQUE constraint hasn’t been created with the option NULLS NOT DISTINCT, NULL value are treated as not equal with another NULL value; this implies that multiple rows can exist where column values defined by the constraint have equal, NOT NULL values, as long as one or more column values defined by the constraint have a NULL value (see Example 4 below). If the UNIQUE constraint is defined by only one column, multiple rows can exist where that column value is equal to NULL.

The FOREIGN KEY constraint defines a relationship between one or more columns in the new table, named as column_name, with referencing column(s), named as reference_column, in the referenced table, named as reference_table. See the parameters section below for rules on specifying the reference_table and reference_column for the FOREIGN KEY constraint.

Except primary keys, to drop a constraint on a table, use:

DROP CONSTRAINT <constraint_name>

You cannot drop a constraint, such as a UNIQUE constraint, on a table if it is being referenced by another table as a FOREIGN KEY. You must first alter the referencing table and drop the foreign key reference to the UNIQUE constraint.

Dropping a UNIQUE INDEX, using the DROP INDEX command will drop the UNIQUE constraint associated with that UNIQUE INDEX.

It is not currently possible to drop a primary key for a table. You must drop the index for the primary key instead (see DROP INDEX). You can only drop a primary key index if it is not being referenced by another table as a FOREIGN KEY.

Altering a Column by Adding or Removing a Column Qualifier

One or more specific column qualifiers can be added or removed for an existing column using ALTER COLUMN.

Using ALTER COLUMN you can do one or more of the following:

  • Change the type of a column

  • Add a collation to an existing column

  • Add a default expression to an existing column. Note, altering an existing column to add a default value will not update existing rows with that default value.

  • Add a NULL or NOT NULL constraint to an existing column

  • Make an existing column a primary key. All existing rows must be not null.

You can also drop a default expression from a column using DROP DEFAULT.

There are five variants of this subcommand:

ALTER [ COLUMN ] column_name
    { [ SET DATA ] TYPE data_type |
      COLLATION | COLLATE [ = ] }{DEFAULT |8859-1U} collation_name |
      SET DEFAULT default_expr |
      column_constraint }...

More than one variant of this subcommand can be specified per statement, separated by a space.

The collation type cannot be altered for a column that has been defined in an index. The recommended approach is to:

  1. Drop the index.

  2. Alter the column using the ALTER TABLE command.

  3. Recreate the index.

Redefining a Column

You can redefine a column using the MODIFY subcommand or rename and redefine a column using the CHANGE subcommand.

MODIFY [ COLUMN ] column_definition

The MODIFY subcommand redefines an existing column. It is the same as CHANGE COLUMN except CHANGE also allows you to rename the existing column. The keyword COLUMN is optional and can be omitted. MODIFY COLUMN requires the same syntax as ADD COLUMN, or when defining a column in the CREATE TABLE command (See CREATE TABLE). Therefore, the data type must be specified — regardless of whether those parameters are being modified or not — along with one or more of the optional column_qualifier s in the column_definition.

You may not use MODIFY COLUMN to redefine a column to or from a computed column.

You can rename and redefine a column using the CHANGE subcommand.

CHANGE [ COLUMN ] column_name new_column_name data_type column_qualifier

The CHANGE subcommand changes the name and other modifiers of an existing column in the table. It is the same as MODIFY COLUMN except it also allows you to rename the column. The keyword COLUMN is optional and can be omitted. CHANGE COLUMN uses the same syntax as ADD COLUMN or when defining a column in the CREATE TABLE statement (See CREATE TABLE). Therefore, the data type of the column must be specified — regardless of whether those parameters are being modified or not — along with one or more of the optional column_qualifier s.

You may not use CHANGE COLUMN to add a CHECK, UNIQUE, or referential FOREIGN KEY constraint to a column.

Renaming a Column

The RENAME COLUMN command modifies the name of an existing column in the table:

RENAME [ COLUMN ] column_name TO new_column_name

The keyword COLUMN can be omitted. There is no effect on the stored data.

Renaming a Table

The RENAME command renames an existing table.

RENAME TO [ schema_name. ]new_table_name

If provided, schema_name must be the same for table_name and new_table_name. Tables cannot be moved between schemas.

Enabling or Disabling an Index

It is possible to temporarily disable an index such that it will not be chosen by the optimizer for use in a query plan. The index will still be updated when inserts are done on its corresponding column or columns. When an index is disabled it is disabled for all existing and new connections to the database and will remain disabled until you use ENABLE INDEX. To disable an index:

DISABLE INDEX <index_name>

To enable an index that has been previously disabled use:

ENABLE INDEX <index_name>

Rebuilding an Index

The REBUILD sub command will drop and create the index using the current index definition. If a non-default resolution was specified at index creation time, that resolution will be used on rebuild. To change the index, such as adding or removing columns, use DROP INDEX and CREATE INDEX.

  • During normal operations, you do not have to manually run the REBUILD sub command because NuoDB already reorganizes and rebalances indexes automatically in the background.

  • Rebuilding indexes can sometimes be necessary when nuochk verifies an archive and identifies that an index needs to be rebuilt.

See also, section Running Concurrent Index Builds on the CREATE INDEX page that describes behavior when many index builds run concurrently.

To rebuild all indexes defined for the table being altered, without changing the current type, use:

REBUILD INDEXES

To rebuild all indexes using B Tree indexing, use:

REBUILD INDEXES USING BTREE

To rebuild all indexes using Merge Tree indexing, use:

REBUILD INDEXES USING MERGE TREE

REBUILD INDEXES also includes rebuilding the primary key index.

To rebuild only the index specified by index_name for the table being altered, without changing the index type, use:

REBUILD INDEX <index_name>

To rebuild index using B Tree indexing, use:

REBUILD INDEX <index_name> USING BTREE

To rebuild index using Merge Tree indexing, use:

REBUILD INDEX <index_name> USING MERGE TREE

Renaming an Index

To modify the name of an existing index in the table, use the following:

RENAME [ INDEX ] <index_name> TO <new_index_name>

There is no effect on the stored data. This operation has no impact on ongoing transactions.

Adding or Dropping a Table Partition

You can add or drop a table partition from an already existing partitioned table. You can also add a partition to an unpartitioned, populated table without moving data.

ADD PARTITION <partition_name> VALUES LESS THAN ( value | MAXVALUE ) [ STORE IN <storage_group> ] }...
ADD PARTITION <partition_name> VALUES IN ( value [ ,value]... ) [ STORE IN <storage_group> ] }...
DROP PARTITION <partition_name>

There is no PARTITION BY clause for ALTER TABLE.

The type of partition (RANGE or LIST) must match the existing partitioning type for the table, that is, the type specified by PARTITION BY when the table was created.

Adding a partition on a range that is already covered by an existing partition defined for the table, will result in a runtime error.

See Table Partitions and Storage Groups for more information.

Converting an Unpartitioned Table to a Partitioned Table

An unpartitioned table can be partitioned using the following:

ALTER TABLE <table_name> MODIFY PARTITION BY RANGE <column_name>
    [STORE IN <default_storage_group>]
    PARTITION <partition_name> VALUES LESS THAN (literal | MAXVALUE)
    [STORE IN <storage_group>]

The resulting partitioned table will have a single partition that contains all the data from the original table, including existing indexes.

Only range-style partitioning is supported. The range must be set high enough to include all records currently in the table. This means the specified range must be greater than the maximum value of partitioning column (column_range) in all records of the table.

If any unique indexes have been created on the table the index keys must be guaranteed to be unique across partitions. This means the index must include the partitioning column or include an autogenerated column.

The partition must remain in the same storage group as the table currently resides. This will be the UNPARTITIONED storage group for a regular table.

It is possible to specify STORE IN default_storage_group and/or STORE IN storage_group as long as the partition will remain in the same storage group as it is currently in. The default storage group is used when adding additional partitions so that there is no need to always specify the storage group.

Advancing the Sequence of an Automatically Generated Column

You can advance the sequence of automatically generated column to match the highest value in the table by issuing the following command:

ALTER TABLE CHANGE COLUMN <column_name> UPDATE SEQUENCE RANGE

Remove the Identity Property of a Generated Column

To remove the identity property of a generated column, use ALTER TABLE ALTER COLUMN DROP IDENTITY or ALTER TABLE ALTER COLUMN DROP IDENTITY IF EXISTS commands.

ALTER TABLE <table_name> ALTER COLUMN <column_name> DROP IDENTITY;

Or

ALTER TABLE <table_name> ALTER COLUMN <column_name> DROP IDENTITY IF EXISTS;

Regenerate a Table Format

During a DELETE, INSERT, REPLACE, or UPDATE operation on a table an error is reported if the format of the newly updated or created record does not match the format of the database table. To regenerate the table format, use the ALTER TABLE REBUILD FORMAT command.

ALTER TABLE <table_name> REBUILD FORMAT;

Move Tables and Partitions to Specific Storage Groups

To move an unpartitioned table to another existing storage group, use the STORE IN syntax as follows:

ALTER TABLE <table_name>
    STORE IN <destination_storage_group>

If the table is partitioned, the individual partitions may be moved to another existing storage group using the MOVE PARTITION syntax as follows:

ALTER TABLE <table_name> MOVE PARTITION <partition_name>
      [STORE IN <storage_group>]

This syntax moves all the data and indexes in the table or partition from the current storage group to the new storage group (storage_group). The ALTER TABLE command will not return until the operation is complete. The time to finish the operation depends on the size of the table or partition. For more information, see Table Partitions and Storage Groups.

  • The storage group must already exist before moving the table.

  • INSERT, UPDATE, and DELETE operations on the table are still permitted while the move is taking place.

Parameters

schema_name

Name of the schema in which the table to be altered exists. The default is the current schema as defined by the USE command.

table_name

Name (optionally schema-qualified) of an existing table

column_name

Name of an existing column

new_column_name

New name for an existing column

data_type

The data type of the column. The data type specifies the length and scale factor for the column. For more details. see SQL Data Types.

NULL | NOT NULL

A column qualifier that constrains the column to either allow or disallow NULL values.

reference_table

This is the name of an existing table being referenced by a FOREIGN KEY constraint.

reference_column

This is the name of one or more columns defined in the reference_table being referenced by a FOREIGN KEY constraint. The reference_column column list must be a list of columns that are defined as a PRIMARY KEY or UNIQUE constraint of the reference_table. If no reference_column column list is specified, the default is to reference column(s) defined by the PRIMARY KEY for the table specified as the reference_table. This reference_column column list must include all columns defined by the PRIMARY KEY or UNIQUE constraint of the reference_table, but not necessarily in that same order.

boolean_expression

A SQL expression used in a CHECK constraint. The CHECK constraint is satisfied if boolean_expression resolves to true or unknown. A boolean_expression appearing within a CHECK constraint in a column definition should reference that column’s value only, while a boolean_expression appearing as a table constraint may reference multiple columns. The boolean_expression cannot contain sub-selects nor refer to variables other than columns of the current row.

UNIQUE

A column qualifier that specifies a UNIQUE constraint on the column or columns specified. A UNIQUE constraint is satisfied if each value inserted into the column is unique. Adding a UNIQUE constraint will create a UNIQUE INDEX on the column(s) defined by the UNIQUE constraint.

collation_name

A string representing the name of a collation used in a COLLATION or COLLATE qualifier. See Example 2. Supported collation types include: DEFAULT, case_insensitive and "8859-1U". Both case_insensitive and "8859-1U" collation types are synonyms for each other. Therefore, COLLATE case_insensitive will produce the same results as COLLATE "8859-1U". The DEFAULT collation is case-sensitive.

default_expr

A SQL expression used in a DEFAULT column qualifier.

GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [(generator_name)]

A column qualifier that uses a sequence to generate values to be inserted into the given column. See CREATE TABLE for details and usage of generated columns.

Altering an existing column to add the IDENTITY qualifier will not update existing rows.
GENERATED ALWAYS AS (expression) PERSISTED

A column qualifier that is used to define a computed column. See CREATE TABLE for details and usage of computed columns.

It is not possible to modify an existing column to or from a computed column.
constraint_name

Optional name of a table constraint. If not provided, a unique constraint name will be generated.

new_table_name

New name (optionally schema-qualified) of an existing table to be renamed. If provided, schema must be same as existing table. Tables cannot be moved between schemas.

partition_name

The name of a partition.

VALUES LESS THAN ( value | MAXVALUE )

This is used in the partitioning syntax for VALUES LESS THAN value and VALUES IN (value [,value]…​). See CREATE TABLE and Table Partitions and Storage Groups for more information.

The keyword MAXVALUE is used by the syntax VALUES LESS THAN(MAXVALUE) to specify a default partition for the table. See CREATE TABLE and Table Partitions and Storage Groups for more information.

VALUES IN ( value [ ,value ]…​ )

A literal value, whose type should be the same as the corresponding column. This is used in the partitioning syntax for VALUES LESS THAN value and VALUES IN (literal [,literal]…​). See CREATE TABLE and Using Table Partitions and Storage Groups for more information.

The keyword MAXVALUE is used by the syntax VALUES LESS THAN(MAXVALUE) to specify a default partition for the table. See CREATE TABLE and Table Partitions and Storage Groups for more information.

STORE IN storage_group

The name of a storage group. For information about creating a storage group, see Managing Storage Groups. See CREATE TABLE and Table Partitions and Storage Groups for more information.

index_qualifiers

One or more modifiers for an index used in ADD PRIMARY KEY and in the table constraints, KEY, UNIQUE KEY, and PRIMARY KEY, where index_qualifiers is:

( index_qualifier [ , index_qualifier ]…​ )

and index_qualifier is one of:

RESOLUTION n

The query optimizer uses statistics to plan and optimize queries. Index histograms are one of the most important parts of index statistics. This option allows you to specify the RESOLUTION of the histogram, that is, the number of "buckets" used in the histogram. The default number of buckets created is 256. Raising this number will make more precise statistics available to the query optimizer, which will then allow the optimizer to make better decisions, potentially leading to an improvement in query performance. The downside of increasing the number of buckets is that the index will consume more memory and processing when the index is created or updated. Valid RESOLUTION values are in the range: 10 to 1000 (default 256).

See Improving Query Performance for more information on how to use this option.

See Improving Query Performance for more information about using this option.

MAX KEY SIZE literal

The default maximum key size is 1024 bytes. NuoDB recommends that index key size be less than or equal to the default maximum because having larger index keys might decrease performance. However, if your application requires a larger maximum index key size then you can change this using ALTER TABLE. There is no system property for setting a maximum index key size. You can increase the maximum index key size only when you create a particular index. To change the maximum size allowed for an index key, specify WITH(MAX KEY SIZE n) and replace n with a value from 100 bytes to 3072 bytes. NuoDB throws an exception if you try to specify a maximum key size that is less than 100 bytes or more than 3072 bytes.

index_name

Identifier to distinguish an index. This is used by ENABLE and DISABLE INDEX, REBUILD INDEX, and in identifying keys.

Examples

Example 1: Multiple ALTER TABLE examples

Create a new table.
Alter the table to add a new column.
Alter the table to add a table constraint primary key.
This change sets the column to now be NOT NULL.
Alter the table to drop an existing column and add a new column.
Alter the table to modify an existing column
Alter the table to rename an existing column

CREATE TABLE hockey_fans (id       INTEGER     GENERATED ALWAYS AS IDENTITY,
name      STRING     NOT NULL,
address   STRING     NULL,
city      STRING     NOT NULL,
teamid    VARCHAR(3) DEFAULT 'BOS',
gender    CHAR(1)    CHECK (gender IN ('M','F') ),
phone     STRING);

SHOW TABLE hockey_fans;
    Tables named HOCKEY_FANS
    Found table HOCKEY_FANS in schema USER
        Fields:
            ID integer
                Generator: HOCKEY_FANS$IDENTITY_SEQUENCE    Generated Always
            NAME string
                Nullable: No
            ADDRESS string
            CITY string
                Nullable: No
            TEAMID varchar(3)
                Default: 'BOS'
            GENDER char(1)
                Constraint: GENDER in ('M','F')
            PHONE string

ALTER TABLE hockey_fans ADD COLUMN age INTEGER;
ALTER TABLE hockey_fans ADD CONSTRAINT pk_hockey_fans PRIMARY KEY (id);
SHOW table hockey_fans;
    Tables named HOCKEY_FANS
    Found table HOCKEY_FANS in schema USER
        Fields:
            ID integer
                Nullable: No
                Generator: HOCKEY_FANS$IDENTITY_SEQUENCE    Generated Always
            NAME string
                Nullable: No
            ADDRESS string
            CITY string
                Nullable: No
            TEAMID varchar(3)
                Default: 'BOS'
            GENDER char(1)
                Constraint: GENDER in ('M','F')
            PHONE string
            AGE integer
        Primary Index: PK_HOCKEY_FANS on field: ID

ALTER TABLE hockey_fans DROP age , ADD state CHAR(2);
SHOW table hockey_fans;``
    Tables named HOCKEY_FANS
    Found table HOCKEY_FANS in schema USER
        Fields:
            ID integer
                Nullable: No
                Generator: HOCKEY_FANS$IDENTITY_SEQUENCE    Generated Always
            NAME string
                Nullable: No
            ADDRESS string
            CITY string
                Nullable: No
            TEAMID varchar(3)
                Default: 'BOS'
            GENDER char(1)
                Constraint: GENDER in ('M','F')
            PHONE string
            STATE char(2)
        Primary Index: PK_HOCKEY_FANS on field: ID

ALTER TABLE hockey_fans ALTER state SET DATA TYPE STRING;
SHOW table hockey_fans;
    Tables named HOCKEY_FANS
    Found table HOCKEY_FANS in schema USER
        Fields:
            ID integer
                Nullable: No
                Generator: HOCKEY_FANS$IDENTITY_SEQUENCE    Generated Always
            NAME string
                Nullable: No
            ADDRESS string
            CITY string
                Nullable: No
            TEAMID varchar(3)
                Default: 'BOS'
            GENDER char(1)
                Constraint: GENDER in ('M','F')
            PHONE string
            STATE string
        Primary Index: PK_HOCKEY_FANS on field: ID

ALTER TABLE hockey_fans RENAME phone TO phone_number;
SHOW table hockey_fans;
    Tables named HOCKEY_FANS
    Found table HOCKEY_FANS in schema USER
        Fields:
            ID integer
                Nullable: No
                Generator: HOCKEY_FANS$IDENTITY_SEQUENCE    Generated Always
            NAME string
                Nullable: No
            ADDRESS string
            CITY string
                Nullable: No
            TEAMID varchar(3)
                Default: 'BOS'
            GENDER char(1)
                Constraint: GENDER in ('M','F')
            PHONE_NUMBER string
            STATE string
        Primary Index: PK_HOCKEY_FANS on field: ID
Example 2: Alter the collation on a column
DROP TABLE tst_table IF EXISTS;
CREATE TABLE tst_table (column1 VARCHAR(12));
INSERT INTO tst_table VALUES ('abc'),('ABC');
INSERT INTO tst_table VALUES ('bcd'),('BCD');
SELECT * FROM tst_table WHERE column1 = 'abc';
COLUMN1
--------
   abc

SELECT * FROM tst_table ORDER BY column1;
 COLUMN1
 --------
   ABC
   BCD
   abc
   bcd

ALTER TABLE tst_table ALTER column1 COLLATE 8859-1U;
SELECT * FROM tst_table WHERE column1 = 'abc';
 COLUMN1
 --------
   abc
   ABC

SELECT * FROM tst_table ORDER BY column1;
 COLUMN1
 --------
   abc
   ABC
   bcd
   BCD

/* Cannot alter the collation on an indexed column */
USE TEST;
DROP TABLE IF EXISTS tsttable;
CREATE TABLE tsttable (column1 INTEGER, column2 string);
CREATE INDEX idx_tsttable_column2 ON tsttable(column2);
ALTER TABLE tsttable ALTER COLUMN column2 collate 8859-1U;
/* ERROR MSG: cannot modify collation on column column2 in table test.tsttable: column is used in index idx_tsttable_column2 */
Example 3: Alter table adding not null column and providing default value
USE TEST;
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table ( field1 INTEGER PRIMARY KEY, field2 STRING);
INSERT INTO test_table (field1, field2) VALUES (1,'row1');

ALTER TABLE test_table ADD COLUMN field3 STRING;
SELECT * FROM test_table;
 FIELD1  FIELD2  FIELD3
 ------- ------- -------
    1     row1   <null>
SHOW TABLE test_table;
    Tables named TEST_TABLE
    Found table TEST_TABLE in schema USER
        Fields:
            FIELD1 integer
                Nullable: No
            FIELD2 string
            FIELD3 string
        Primary Index: TEST_TABLE..PRIMARY_KEY on field: FIELD1

/* cannot alter an existing column to NOT NULL */
ALTER TABLE test_table ALTER COLUMN field3 NOT NULL;
/* ERROR: NOT NULL constraint failed. Column field3 contains NULL values*/

ALTER TABLE test_table ALTER COLUMN field3 NOT NULL DEFAULT 'field3 not null';
SELECT * FROM test_table;
 FIELD1  FIELD2      FIELD3
 ------- ------- ---------------
    1     row1   field3 not null
/* cannot alter table adding a new NOT NULL column without having to specify a default
 * value */
ALTER TABLE test_table ADD COLUMN field4 STRING NOT NULL;
/* ERROR:  field FIELD4 doesn't allow NULL values, but a default value for the
 * existing rows hasn't been specified */

ALTER TABLE test_table ADD COLUMN field4 STRING NOT NULL DEFAULT 'userid';
SELECT * FROM test_table;
 FIELD1  FIELD2      FIELD3      FIELD4
 ------- ------- --------------- -------
    1     row1   field3 not null userid
Example 4: Alter table adding PRIMARY KEY or UNIQUE constraint

First, we attempt to add a PRIMARY KEY constraint on columns with NULL values.
This returns an error.
The UPDATE statement sets these NULL values to some unique, NOT NULL value.
Adding the PRIMARY KEY also changes the columns defined by the PRIMARY KEY to be NOT NULL.

USE TEST;
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table ( field1 INTEGER, field2 INTEGER, field3 STRING );
INSERT INTO test_table (field1, field2, field3) VALUES (NULL,1,'row1'),(NULL,1,'row2');
SHOW TABLE test_table;
    Tables named TEST_TABLE

    Found table TEST_TABLE in schema TEST

        Fields:
            FIELD1 integer
            FIELD2 integer
            FIELD3 string

ALTER TABLE test_table ADD PRIMARY KEY (field1,field2);
Null key values are not allowed in primary key index TEST_TABLE..PRIMARY_KEY

UPDATE test_table SET field1 = substr(field3,-1,1) where field1 IS NULL;
SELECT * FROM test_table;
 FIELD1  FIELD2  FIELD3
 ------- ------- -------

    1       1     row1
    2       1     row2

ALTER TABLE test_table ADD PRIMARY KEY (field1,field2);
SHOW TABLE test_table;

    Tables named TEST_TABLE

    Found table TEST_TABLE in schema TEST

        Fields:
            FIELD1 integer
                Nullable: No
            FIELD2 integer
                Nullable: No
            FIELD3 string
        Primary Index: TEST_TABLE..PRIMARY_KEY on fields: FIELD1, FIELD2

Using the same original column values, the following shows the behavior of adding a UNIQUE constraint.
The two rows appear to be duplicate rows, both having the same values of field1 equal to NULL value and field2 equal to 1.
However, since NULL values cannot be equal values, these two rows are unique. Therefore, the UNIQUE constraint is created.
The UNIQUE constraint allows NULL values. Therefore, the columns defined by the UNIQUE constraint are not changed to NOT NULL.

USE TEST;
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table ( field1 INTEGER, field2 INTEGER, field3 STRING );
INSERT INTO test_table (field1, field2, field3) VALUES (NULL,1,'row1'),(NULL,1,'row2');
SHOW TABLE test_table;

    Tables named TEST_TABLE

    Found table TEST_TABLE in schema TEST

        Fields:
            FIELD1 integer
            FIELD2 integer
            FIELD3 string

ALTER TABLE test_table ADD unique (field1,field2);
SHOW TABLE test_table;

    Tables named TEST_TABLE

    Found table TEST_TABLE in schema TEST

        Fields:
            FIELD1 integer
            FIELD2 integer
            FIELD3 string
        Unique Constraint Index: TEST_TABLE_UNIQUE_0 on fields: FIELD1, FIELD2
Example 5: Updating an automatically generated sequence to match the highest value in the column
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table(a INT GENERATED BY DEFAULT AS IDENTITY (auto_seq));
SELECT NEXT VALUE FOR auto_seq FROM DUAL;
AUTO_SEQ
---------
    1
INSERT INTO test_table VALUES();
INSERT INTO test_table VALUES();
INSERT INTO test_table VALUES();
AUTO_SEQ
---------
    3
ALTER TABLE test_table CHANGE COLUMN a UPDATE SEQUENCE RANGE;
SELECT NEXT VALUE FOR auto_seq FROM DUAL;
AUTO_SEQ
---------
  1001
Example 6: Converting an unpartitioned table to a partitioned table
/* Create a regular table and then convert it to a partitioned table */
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table(f1 int, f2 int, f3 string, PRIMARY KEY (f1, f2));
INSERT INTO test_table values (1,100,'abc'), (2,200,'def'), (3,300,'ghi');

/* Unique indexes must include the partitioning column or an autogenerated
 * column */
ALTER TABLE test_table MODIFY PARTITION BY RANGE (f3)
    PARTITION p1 VALUES LESS THAN ('zzz') STORE IN DEFAULT;
/* Error:  Partitioning column TEST_TABLE.F3 must be part of unique index
 * TEST_TABLE..PRIMARY_KEY, or TEST_TABLE..PRIMARY_KEY must include an
 * autogenerated column */

/* The range must exceed the maximum value in the partitioning column */
ALTER TABLE test_table MODIFY PARTITION BY RANGE (f2)
    PARTITION p1 VALUES LESS THAN (200) STORE IN DEFAULT;
/* Error:  partition range must exceed maximum value in the table */

SQL> SELECT MAX(f2) FROM test_table;
 MAX
 ----
 300

ALTER TABLE test_table MODIFY PARTITION BY RANGE (f2)
    PARTITION p1 VALUES LESS THAN (301) STORE IN DEFAULT;

/* The table is now partitioned */
SELECT tablename, partitioningcolumn, defaultstoragegroup
    FROM system.partitionedtables WHERE tablename = 'TEST_TABLE';

 TABLENAME  PARTITIONINGCOLUMN  DEFAULTSTORAGEGROUP
 ---------- ------------------- --------------------
 TEST_TABLE         F2                 <null>

SELECT tablename, partitionname, storagegroup
    FROM system.partitionids WHERE tablename = 'TEST_TABLE';

 TABLENAME  PARTITIONNAME  STORAGEGROUP
 ---------- -------------- -------------
 TEST_TABLE       P1       UNPARTITIONED


/* Convert a table that is located on a specific storage group */
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table(f1 int, f2 int, f3 string) STORE IN stg1;
INSERT INTO test_table values (1,100,'abc'), (2,200,'def'), (3,300,'ghi');

/* The storage group cannot be changed */
ALTER TABLE test_table MODIFY PARTITION BY RANGE (f2)
    PARTITION p1 VALUES LESS THAN (301) STORE IN DEFAULT;
/* Error:  changing the storage group is disallowed, table TEST_TABLE is
 * located in storage group STG1 */

ALTER TABLE test_table MODIFY PARTITION BY RANGE (f2) STORE IN stg2
    PARTITION p1 VALUES LESS THAN (301);
/* Error:  changing the storage group is disallowed, table TEST_TABLE is
 * located in storage group STG1 */

/* A different default storage group may be specified
   which will then be used by add partition */
ALTER TABLE test_table MODIFY PARTITION BY RANGE (f2) STORE IN stg2
    PARTITION p1 VALUES LESS THAN (301) STORE IN stg1;

ALTER TABLE test_table ADD PARTITION p2 VALUES LESS THAN (401);
SELECT tablename, partitioningcolumn, defaultstoragegroup
    FROM system.partitionedtables WHERE tablename = 'TEST_TABLE';

 TABLENAME  PARTITIONINGCOLUMN  DEFAULTSTORAGEGROUP
 ---------- ------------------- --------------------
 TEST_TABLE         F2                  STG2

SELECT tablename, partitionname, storagegroup
    FROM system.partitionids WHERE tablename = 'TEST_TABLE';

 TABLENAME  PARTITIONNAME  STORAGEGROUP
 ---------- -------------- -------------
 TEST_TABLE       P2           STG2
 TEST_TABLE       P1           STG1