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_typedomain_name } |
      COLLATION | COLLATE [ = ] } collation_name |
      SET DEFAULT default_expr |
      column_constraint }...

CHANGE [ COLUMN ] column_name new_column_name { data_typedomain_name } column_qualifier

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

REBUILD { INDEXES | INDEX index_name }

RENAME [ COLUMN ] column_name TO new_column_name
RENAME TO [ schema_name. ]new_table_name

Where column_definition is:

column_name { data_typedomain_name } column_qualifier

Where column_qualifier can be one or more of the following:

column_constraint
{ COLLATION | COLLATE [ = ] } collation_name
DEFAULT expression
GENERATED ALWAYS AS IDENTITY | GENERATED BY DEFAULT AS IDENTITY

and column_constraint is one or more of the following:

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

Where table_constraint can be one of the following:

CHECK ( boolean_expression )
UNIQUE ( column_name [, ...] )
[ UNIQUE ] KEY index_name ( column_name [ ,column_name ]... ) [ index_qualifiers ] ) ]
PRIMARY KEY ( column_name [ ,column_name ]... ) [ index_qualifiers ]

Description

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

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).

Note: 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.

You cannot drop a column that is defined in an index.

You cannot drop a partitioning column, that is a column used in table partitioning.

You cannot drop a column that is referenced in a table constraint. You must first drop the table constraint.

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 uniquely 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....

Note: 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. Since a NULL value cannot equal another NULL value, 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.

With the exception of 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. ALTER COLUMN is similar to MODIFY COLUMN, except ALTER allows you to just change one or more qualifiers for the column and MODIFY redefines the column alogether.

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

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_typedomain_name } |
      COLLATION | COLLATE [ = ] } collation_name |
      SET DEFAULT default_expr |
      column_constraint }...

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

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

Note: 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 or the domain definition must be specified — regardless of whether those parameters are being modified or not — along with one or more of the optional column_qualifiers in the column_definition.

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

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

CHANGE [ COLUMN ] column_name new_column_name { data_typedomain_name } 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 is similar to MODIFY COLUMN with the additional functionality of renaming the column. 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 or the domain definition 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_qualifiers.

Note: 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 subcommand will drop and create the index using the current index definition. To change the index, such as adding or removing columns, use DROP INDEX and CREATE INDEX (See DROP INDEX and CREATE INDEX). Rebuilding indexes can sometimes be necessary when nuochk verifies an archive and identifies that an index needs to be rebuilt (See NuoDB Check).

To rebuild every index defined for the table being altered, use:

REBUILD INDEXES			

This also includes rebuilding the primary key index.

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

REBUILD INDEX index_name		

Adding or Dropping a Table Partition

You can add or drop a table partition from an already existing partitioned table. It is not possible to add partitions to an already existing unpartitioned table.

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 ] }...
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 Using Table Partitions and Storage Groups for more information.

Parameters

Examples