DB2 - SQL Alter Table Statement

The SQL ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

Following is the table alteration syntax:

ALTER TABLE table-name
    [ADD column-definition
         table-constraint-clause]
    [ALTER COLUMN column-alteration]
    [DROP CONSTRAINT constraint-name|
          PRIMARY KEY|
          UNIQUE (column-name [,column-name...])]
    [RENAME COLUMN source-column-name
                TO target-column-name]

column-alteration
    [SET DATA TYPE (altered-data-type)]
    [SET default-clause]
    [DROP DEFAULT]

Example 1:

Column DEPT_NAME in table DSN8910.TB_DEPARTMENT was created as a VARCHAR(36). Increase its length to 60 bytes. Also, add the column DEPT_BLDG to the table DSN8910.TB_DEPARTMENT. Describe the new column as a character string column of length is 5.

ALTER TABLE DSN8910.TB_DEPARTMENT
      ALTER COLUMN DEPT_NAME
            SET DATA TYPE VARCHAR(60)
      ADD DEPT_BLDG CHAR(5);

Example 2:

Alter the TB_PRODINFO table to define a foreign key that references a non-primary unique key in the product version table (TB_PRODVER). The columns of the unique key are PRODVER_NAME and PRODVER_RELNO.

ALTER TABLE TB_PRODINFO
      FOREIGN KEY (PRODINFO_NAME, PRODINFO_VERNO)
      REFERENCES TB_PRODVER (PRODVER_NAME, PRODVER_RELNO)
      ON DELETE RESTRICT;

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!