ALTER TABLE

Use the ALTER TABLE command to change the structure of an existing table. If the table is in use by an active query, the ALTER command waits until that query completes.

Use the ALTER TABLE command to:

Change or drop a column default. Defaults that you set apply only to subsequent INSERT commands, not to rows already in the table.
Rename a column or a table without changing the data type or size within the column or table. You can omit the keyword column.
Add or drop a table constraint or column constraint. You cannot change a constraint. You must instead drop the constraint and create a new one.
Modify the length of a varchar column.

If a table is referenced by a stored procedure, adding or dropping a column is not allowed. You must first drop the stored procedure before you run the ALTER TABLE command, and then re-create the stored procedure after the table is altered.

Syntax

Syntax for altering a table:

ALTER TABLE <table> <action>

Where <action> can be one of:

ADD COLUMN <col> <type> [<col_constraint>][,…] |
ADD <table_constraint> |
ALTER [COLUMN] <col> { SET DEFAULT <value> | DROP DEFAULT } |
DROP [COLUMN] column_name[,column_name…] {CASCADE | RESTRICT } |
DROP CONSTRAINT <constraint_name> {CASCADE | RESTRICT} |
MODIFY COLUMN (<col> VARCHAR(<maxsize>)) |
ORGANIZE ON {(<columns>) | NONE} |
OWNER TO <user_name> |
RENAME [COLUMN] <col> TO <new_col_name> |
RENAME TO <new_table> |
SET PRIVILEGES TO <table>  |
DATA_VERSION_RETENTION_TIME <number-of-days>

Where <col_constraint> represents:

[ CONSTRAINT <constraint_name> ] 
{NOT NULL | NULL | UNIQUE | PRIMARY KEY | DEFAULT <value> | <ref>}
[ [ [ NOT ] DEFERRABLE ] { INITIALLY DEFERRED | INITIALLY IMMEDIATE } |
[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ NOT ] DEFERRABLE ]

Where <table_constraint> represents:

[ CONSTRAINT <constraint_name> ] 
{UNIQUE (<col>[,<col>…] ) |
PRIMARY KEY (<pkcol_name>[,<pkcol_name>…] ) |
FOREIGN KEY (<fkcol_name>[,<fkcol_name>…] ) <ref>}
[ [ [ NOT ] DEFERRABLE ] { INITIALLY DEFERRED | INITIALLY IMMEDIATE } |
[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ NOT ] DEFERRABLE ]

Where <ref> represents:

REFERENCES <reftable> [ (<refcol_name>[,<refcol_name>…] ) ]
[ MATCH FULL ]
[ ON UPDATE {CASCADE | RESTRICT | SET NULL | SET DEFAULT | NO ACTION} ]
[ ON DELETE {CASCADE | RESTRICT | SET NULL | SET DEFAULT | NO ACTION} ]

Inputs

The ALTER TABLE command takes the following inputs:

Table 1. ALTER TABLE inputs
Input	Description
<table>	The name of the table to be altered.
<action>	The action that is to be carried out for the specified table: ADD COLUMN Add a column. This input cannot be specified in a transaction block. If the table has a nonzero DATA_VERSION_RETENTION_TIME specified, the command fails. For more information, see Netezza Performance Server time travel. ADD <table_constraint> Add a table constraint. ALTER [COLUMN] Change or drop a column default value. DROP [COLUMN] Drop a column. Any constraints that rely on the dropped column are automatically dropped as well. This input cannot be specified in a transaction block. You cannot drop a distribution column, an "organize on" column, or the last remaining column in a table. If you drop a column and want to reuse its name, first run the GROOM TABLE <tablename> VERSIONS command; otherwise, errors might occur. If the table has a nonzero DATA_VERSION_RETENTION_TIME specified, the command fails. For more information, see Netezza Performance Server time travel. DROP CONSTRAINT Drop a constraint. MODIFY COLUMN Change a column length. ORGANIZE ON Specifies which columns (from one to four) the table is to be organized on. This cannot be specified for external tables. If columns are specified, the table cannot have any materialized views, and all specified column data types must be zone-mappable. The table data reorganization takes effect when GROOM TABLE is run. OWNER TO Change the table owner. RENAME [COLUMN] Rename a column. RENAME TO Rename the table. SET PRIVILEGES TO Set the privileges of the altered table to those of another table.
<col>	The name of a column.
<user_name>	The name of a user.
<new_col_name>	The new name to be given a column.
<new_table>	The new name of the table.
<constraint_name>	The name that is to be given to a column constraint or table constraint. If you do not specify a name, the system generates one.
NOT DEFERRABLE \| DEFERRABLE	Controls whether the constraint can be deferred to the end of the transaction. NOT DEFERRABLE is the default. (Netezza Performance Server does not support constraint checking and referential integrity.)
INITIALLY	Specifies either DEFERRED (at the end of the transaction) or IMMEDIATE (at the end of each statement).
NOT NULL \| NULL	Whether the column is allowed to contain null values. NULL is the default.
UNIQUE (column and table constraint)	Whether each value in the column must be unique.
PRIMARY KEY (column and table constraint)	Whether the specified columns are to form the primary key of the table. This constraint is essentially a combination of the UNIQUE and NOT NULL constraints, but identifying a set of columns as a primary key also provides metadata about the design of the schema. A primary key implies that other tables can rely on this set of columns as a unique identifier for rows. You can specify only one primary key constraint for a table, either as a column constraint or as a table constraint. The set of columns that make up the primary key must be different from any other set of columns that is named by any unique constraint defined for the table.
DEFAULT (column constraint)	The default value that is to be placed into each row for this column.
REFERENCES (column constraint)	The specified columns of the new table must only contain values that match values in the specified columns of the specified table.
FOREIGN KEY and REFERENCES table (table constraint)	The specified columns of the new table must only contain values that match values in the specified columns of the specified table. If you do not specify a column, the value must match the primary key of the table. The specified columns of the referenced table must have a unique or primary key constraint in that table.
MATCH FULL	MATCH FULL prevents one column of a multicolumn foreign key from being null if other parts of the foreign key are not null. This is the default. MATCH PARTIAL is unsupported.
ON UPDATE \| ON DELETE	The action that is to be taken when the specified table or columns are updated or deleted: NO ACTION Issues an error if the foreign key is violated. This is the default. RESTRICT Same as NO ACTION. CASCADE Deletes any rows that reference the deleted row. SET NULL Sets the referencing column values to their default value. SET DEFAULT Sets the referencing column values to their default value.
DATA_VERSION_RETENTION_TIME	Alters the retention time on a table. If the retention time is zero, you must specify a nonzero a nonzero DATA_VERSION_RETENTION_TIME to run time travel queries. If you want to alter DATA_VERSION_RETENTION_TIME to a nonzero value for a table that is a temporary, row-secure, versioned, or an external table, the command fails. The maximum allowed value is 99 days. If DATA_VERSION_RETENTION_TIME for a table is set to 0, the table is no longer a temporal table, and no historical data for the table is available for time travel queries. You cannot run time travel queries for tables with retention time set to 0. For more information about time travel and DATA_VERSION_RETENTION_TIME, see Getting started with time travel.

Outputs

The ALTER TABLE command produces the following outputs:

Table 2. ALTER TABLE outputs
Output	Description
`ALTER`	The command was successful.
`ERROR`	The specified table or column is not available.

Privileges

You must be the admin user, the table owner, the owner of the database or schema where the table is defined, or your account must have Alter privilege for the table or for the Table object class. If you are changing the owner of the table, you must have List access to the user account.

Usage

The following provides sample usage:

Drop the default from a column:

MYDB.SCH1(USER)=> ALTER TABLE distributors ALTER COLUMN address DROP
DEFAULT;

Change the length of the varchar for a column:

MYDB.SCH1(USER)=> ALTER TABLE t3 MODIFY COLUMN (col1 VARCHAR(6));

Change the name of a column:

MYDB.SCH1(USER)=> ALTER TABLE distributors RENAME COLUMN address TO
city;

Change the name of a table:

MYDB.SCH1(USER)=> ALTER TABLE distributors RENAME TO suppliers;

Change the table owner:

MYDB.SCH1(USER)=> ALTER TABLE distributors OWNER TO carmen;

Change the privileges to those that are set for another table:

MYDB.SCH1(USER)=> ALTER TABLE distributors SET PRIVILEGES TO suppliers;

Add a constraint:

MYDB.SCH1(USER)=> ALTER TABLE distributors ADD CONSTRAINT empkey
PRIMARY KEY(col1) INITIALLY IMMEDIATE;

Drop a constraint:

MYDB.SCH1(USER)=> ALTER TABLE distributors DROP CONSTRAINT empkey
CASCADE;

Changing temporal tables to nontemporal tables:

ALTER TABLE PRODUCT DATA_VERSION_RETENTION_TIME 0;

Changing nontemporal tables to temporal tables:
```
ALTER TABLE PRODUCT DATA_VERSION_RETENTION_TIME 30;
```
If you first disabled your temporal table and then converted the same table to a temporal table, you do not have access to the prior historical rows for that table. Historical data is collected when rows are deleted or updated after the table is converted to temporal.