ALTER TABLE

Use the ALTER TABLE statement to add columns and constraints to an existing table, remove them from a table, or modify other table features such as AsyncEventListener implementations and gateway sender configurations.

Note: You can perform table modifications only on non-primary key columns that are not used for table partitioning.
Note: See also ALTER TABLE Limitations.

Syntax

ALTER TABLE table-name
{
  ADD COLUMN column-definition |
  ADD table-constraint |
  ALTER [ COLUMN ] column-name SET GENERATED ALWAYS AS IDENTITY |
  DROP [ COLUMN ] column-name [ RESTRICT ] |
  DROP { PRIMARY KEY | FOREIGN KEY constraint-name | UNIQUE 
    constraint-name | CHECK constraint-name | CONSTRAINT constraint-name } |
  SET EVICTION MAXSIZE integer-constant |
  SET EVICTION FREQUENCY integer-constant { SECONDS | HOURS | DAYS } [ START { D 'date-constant' | T 'time-constant' | TS 'timestamp-constant' } ] |
  SET GATEWAYSENDER ( [sender-name] [, sender-name] * ) |
  SET ASYNCEVENTLISTENER ( [listener-name] [, listener-name] * )
}

Description

See also ALTER TABLE Limitations.

See also Identity Columns.

Before GemFire XD executes an ALTER TABLE statement, it flushes any gateway sender or AsyncEventListener queues associated with the table in order to empty all pending table events. In a WAN configuration, if a remote site is unavailable for processing queued events, then GemFire XD may wait for 5 minutes or longer (up to the value of gemfirexd.max-lock-wait) before timing out. You can optionally use the SYS.WAIT_FOR_SENDER_QUEUE_FLUSH procedure to flush all queues before you execute the ALTER TABLE statement.

Adding columns and table-level constraints follows the same syntax as the CREATE TABLE statement.

When you add columns with the ADD COLUMN clause, you can also place a column constraint on the new column, as shown above. However, you can add a column with a NOT NULL constraint to an existing table only if a default value is provided; otherwise an exception is thrown.

When you add constraints, the existing table data is verified to satisfy the constraint. If the existing data violates the constraint, the alteration fails and a constraint violation exception is thrown.

Dropping a column from the table can throw a constraint violation if related object data has become invalid. This behavior applies to the RESTRICT clause, which is used by default. CASCADE deletes are not supported. The schema objects that can cause a DROP COLUMN RESTRICT to be rejected include views, triggers, primary key constraints, foreign key constraints, unique key constraints, check constraints, and column privileges.

You cannot drop the last remaining column in a table. Also, DROP COLUMN is not allowed if gemfirexd.sql-authorization is true. When a column is dropped, it is removed from any indexes that contain it, and the indexes are rebuilt if required. If that column was the only column in the index, then the entire index is dropped.

The DROP CONSTRAINT clause drops a constraint on an existing table. To drop an unnamed constraint, specify the generated constraint name stored in SYS.SYSCONSTRAINTS as a delimited identifier. Dropping a primary key constraint, unique constraint, or a foreign key constraint drops the physical index that enforces the constraint.

You can use the ALTER COLUMN clause after importing existing identity column values (in a non-identity column) to change the column to a GENERATED ALWAYS AS IDENTITY column. (GENERATED ALWAYS identity columns do not allow manual insertion of identity values.) After executing the statement, GemFire XD automatically generates identity values for new rows, and ensures that new identity values are greater than the last imported value at the time you executed the ALTER TABLE command. See write-schema-to-db and write-data-to-db for more information. See also Auto-Generated Column Limitations.

The SET EVICTION MAXSIZE clause enables you to change the eviction LRUMEMSIZE setting for the table. See EVICTION BY Clause.
Note: For partitioned tables, Pivotal recommends that you set both LRUMEMSIZE and MAXPARTSIZE to the same value.
SET EVICTION FREQUENCY can be used only for HDFS read/write tables that specified an EVICTION BY CRITERIA clause with an EVICTION FREQUENCY. You can use this clause in ALTER TABLE to change the eviction frequency, or to set a fixed point in time to begin evicting table data from memory (the START clause). You cannot use ALTER TABLE to change the EVICTION BY CRITERIA, or to change from an EVICTION FREQUENCY to EVICT INCOMING. See EVICTION BY CRITERIA Clause.
Note: If you use the START clause, you must specify times using the GMT (Greenwich Mean Time) time zone.

The SET GATEWAYSENDER and SET ASYNCEVENTLISTENER clauses enable you to change or remove the gateway senders and asynceventlisteners associated with a table. The new list of senders or listeners that you specify in each clause replaces the current configuration. Do not specify a name to remove all gateway sender or AsyncEventListener configurations from a table, as shown in the examples below.

Example

–- create a table with no constraints
CREATE TABLE trade.customers (
    cid int not null,
    cust_name varchar(100),
    addr varchar(100),
    tid int);
-- add a primary key constraint with no data or buckets in the table
ALTER TABLE trade.customers add constraint cust_pk primary key (cid);

–- add a new unique key constraint
ALTER TABLE trade.customers add constraint cust_uk unique (tid);

-- add a new foreign key constraint to a child table;
–- each row is checked to make sure it satisfies the new constraint
CREATE TABLE trade.portfolio (
    cid int not null,
    sid int not null,
    qty int not null,
    availQty int not null,
    tid int,
    constraint portf_pk primary key (cid, sid));
ALTER TABLE trade.portfolio add constraint
    cust_fk foreign key (cid) references trade.customers (cid)
      on delete restrict;

–- drop the unique and foreign key constraints added above
ALTER TABLE trade. customers drop constraint cust_uk;
ALTER TABLE trade.portfolio drop constraint cust_fk;

-- drop a non-primary key column if the column is not used for table partitioning, and the column has no dependents
ALTER TABLE trade.customers drop column addr;

–- add the column back with a default value
ALTER TABLE trade.customers add column addr varchar(100);

-- change a non-identity column with existing identity values to GENERATED ALWAYS AS IDENTITY
ALTER TABLE maps ALTER COLUMN map_id SET GENERATED ALWAYS AS IDENTITY;

-- add a gateway sender configuration to a table that was created without any senders
ALTER TABLE maps SET GATEWAYSENDER (uksender);

-- add a second gateway sender configuration to the "maps" table:
ALTER TABLE maps SET GATEWAYSENDER (uksender, apacsender);

-- remove one gateway sender configuration from the "maps" table:
ALTER TABLE maps SET GATEWAYSENDER (apacsender);

-- remove all gateway sender configurations from "maps:"
ALTER TABLE maps SET GATEWAYSENDER ();

-- change the eviction frequency parameters for an HDFS read/write table:
ALTER TABLE hdfstable SET EVICTION FREQUENCY 10 SECONDS;

See also ALTER TABLE Limitations.

Parent topic: Statements