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. 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 [IF EXISTS] qname ADD [COLUMN] column_name { data_type [ column_option ... ] | SERIAL | BIGSERIAL }
      |  ALTER TABLE [IF EXISTS] qname ALTER [COLUMN] column_name SET [NOT] NULL
      |  ALTER TABLE [IF EXISTS] qname ALTER [COLUMN] column_name SET DEFAULT value
      |  ALTER TABLE [IF EXISTS] qname ALTER [COLUMN] column_name DROP DEFAULT
      |  ALTER TABLE [IF EXISTS] qname ALTER [COLUMN] column_name SET STORAGE {string | NULL}
      |  ALTER TABLE [IF EXISTS] qname DROP [COLUMN] column_name [RESTRICT | CASCADE]
      |  ALTER TABLE [IF EXISTS] qname ADD [ CONSTRAINT ident ]  table_constraint_type
      |  ALTER TABLE [IF EXISTS] qname DROP CONSTRAINT ident [RESTRICT | CASCADE]

Change of the data type of a column is not supported. Instead use command sequence:

  ALTER TABLE tbl ADD COLUMN new_column _new_data_type_;
  UPDATE tbl SET new_column = CONVERT(old_column, _new_data_type_);
  ALTER TABLE tbl DROP COLUMN old_column RESTRICT;
  ALTER TABLE tbl RENAME COLUMN new_column TO old_column;

Note: You cannot drop a column if it is referenced (e.g. from a view, an index, a merge table, a trigger, a foreign key constraint, a function or procedure or another db object except comments). Use option CASCADE to specify to also drop those referencing objects.

You can also change the name of a column, the name of the table itself or the schema the table is belonging to, provided there are no objects which depend on the table or column name. To move a table to a different schema use command: ALTER TABLE ... SET SCHEMA ...

alter_rename_statement:

         ALTER TABLE [IF EXISTS] qname RENAME [COLUMN] column_name TO new_column_name
      |  ALTER TABLE [IF EXISTS] qname RENAME TO new_table_name
      |  ALTER TABLE [IF EXISTS] qname SET SCHEMA schema_name

Only one PRIMARY KEY constraint can be defined per table. When a primary key constraint is added, all the primary key columns will become NOT NULLable implicitly. If no constraint_name is specified a constraint_name will be composed implicitly from the table name, column name(s) and constraint type.

alter-constraint-statement:
  ALTER TABLE [ IF EXISTS ] [ _schema_name_ . ] _table_name_
   ADD [ CONSTRAINT _constraint_name_ ]
   { PRIMARY KEY ( _column_name_ [ , _column_name_ ] [, ... ] )
   |      UNIQUE ( _column_name_ [ , _column_name_ ] [, ... ] )
   | FOREIGN KEY ( _column_name_ [ , _column_name_ ] [, ... ] ) REFERENCES [ _schema_name_ . ] _table_name_
    [ ( _column_name_ [ , _column_name_ ] [, ... ] ) ]   [ match_options ]   [ ref_actions ]
   }

To remove a NOT NULL column constraint use: ALTER TABLE sch.tbl ALTER COLUMN column_name SET NULL. The CHECK constraint is not (yet) supported.

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_access_statement:
         ALTER TABLE [IF EXISTS] qname SET READ ONLY
      |  ALTER TABLE [IF EXISTS] qname SET INSERT ONLY
      |  ALTER TABLE [IF EXISTS] qname SET READ WRITE

The ALTER statement has been extended for merge tables with ADD TABLE and DROP TABLE options to attach and detach a partition table to/from a MERGE TABLE and with SET TABLE p AS PARTITION to change the partitioning specification of the partition table.

alter_statement_for_merge_tables:

        ALTER TABLE [IF EXISTS] qname ADD TABLE qname [ AS PARTITION partition_spec ]
      | ALTER TABLE [IF EXISTS] qname SET TABLE qname AS PARTITION partition_spec
      | ALTER TABLE [IF EXISTS] qname DROP TABLE qname [ RESTRICT | CASCADE ]

Example

ALTER TABLE "tblnm" ADD PRIMARY KEY ("C1_id");
ALTER TABLE if exists "schnm"."tblnm" ADD CONSTRAINT "tblnm_uc" UNIQUE ("name", "desc");
ALTER TABLE "tblnm" ADD CONSTRAINT "tblnm_fk1" FOREIGN KEY ("f_id", "f_seq")
                        REFERENCES "schnm2"."fun" ("id", "seq")  ON UPDATE RESTRICT  ON DELETE CASCADE;