Skip to main content

Alter statement

The ALTER statement can be used to change the TABLE properties, it requires authorization to do so. Addition of a column follows the same syntax and functionality as the CREATE TABLE statement. This form can be used to remove individual columns from a table. If the table is used in a foreign key relationship the actions to be take can be further specified as RESTRICT to forbid it if there are external references to the column values. The CASCADE nullifies all references to the values being removed.

alter_statement:

         ALTER TABLE qname ADD    [COLUMN] { column_def | table_constraint }
      |  ALTER TABLE qname ALTER [COLUMN] ident SET DEFAULT value
      |  ALTER TABLE qname ALTER [COLUMN] ident SET [NOT] NULL
      |  ALTER TABLE qname ALTER [COLUMN] ident DROP DEFAULT
      |  ALTER TABLE qname ALTER [COLUMN] ident SET STORAGE {string | NULL}
      |  ALTER TABLE qname DROP [COLUMN] ident [RESTRICT | CASCADE]
      |  ALTER TABLE qname DROP CONSTRAINT ident [RESTRICT | CASCADE]
 

An individual table can be protected using the READ ONLY and INSERT ONLY mode. All attempts to update are flagged as a SQL error. The reverse operation is ALTER TABLE qname READ WRITE, which makes the table accessible for all update operations.

        | ALTER TABLE qname SET { { READ | INSERT } ONLY | READ WRITE }

The ALTER statement has been extended with ADD TABLE and DROP TABLE options to allow adding/removing partition tables to/from a MERGE TABLE.

   | ALTER TABLE qname ADD TABLE qname
   | ALTER TABLE qname DROP TABLE ident [ RESTRICT | CASCADE ]