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> [ORGANIZE ON {(<columns>) | NONE}]
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>)) |
OWNER TO <user_name> |
RENAME [COLUMN] <col> TO <new_col_name> |
RENAME TO <new_table> |
SET PRIVILEGES TO <table>
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.
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.

DROP CONSTRAINT
Drop a constraint.
MODIFY COLUMN
Change a column length.
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.
ORGANIZE ON 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. For more information, see "Using Clustered Base Tables" in the IBM® Netezza® System Administrator’s Guide.
<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.

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 thetable 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 the 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;