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]
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);
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!