Example 1: Add a new column named RATING, which is one character long, to the DEPARTMENT table.
ALTER TABLE DEPARTMENT ADD RATING CHAR
Example 2: Add a new column named PICTURE_THUMBNAIL to the EMPLOYEE table. Create PICTURE_THUMBNAIL as a BLOB column with a maximum length of 1K characters.
ALTER TABLE EMPLOYEE ADD PICTURE_THUMBNAIL BLOB(1K)
Example 3: Assume a new table EQUIPMENT has been created with the following columns:
Add a referential constraint to the EQUIPMENT table so that the owner (EQUIP_OWNER) must be a department number (DEPTNO) that is present in the DEPARTMENT table. If a department is removed from the DEPARTMENT table, the owner (EQUIP_OWNER) values for all equipment owned by that department should become unassigned (or set to null). Give the constraint the name DEPTQUIP.
ALTER TABLE EQUIPMENT FOREIGN KEY DEPTQUIP (EQUIP_OWNER) REFERENCES DEPARTMENT ON DELETE SET NULL
Change the default value for the EQUIP_OWNER column to 'ABC'.
ALTER TABLE EQUIPMENT ALTER COLUMN EQUIP_OWNER SET DEFAULT 'ABC'
Drop the LOCATION column. Also drop any views, indexes, or constraints that are built on that column.
ALTER TABLE EQUIPMENT DROP COLUMN LOCATION CASCADE
Alter the table so that a new column called SUPPLIER is added, the existing column called LOCATION is dropped, a unique constraint over the new column SUPPLIER is added, and a primary key is built over the existing column EQUIP_NO.
ALTER TABLE EQUIPMENT ADD COLUMN SUPPLIER INT DROP COLUMN LOCATION ADD UNIQUE SUPPLIER ADD PRIMARY KEY EQUIP_NO
Notice that the column EQUIP_DESC is a variable length column. If an allocated length of 25 was specified, the following ALTER TABLE statement would not change that allocated length.
ALTER TABLE EQUIPMENT ALTER COLUMN EQUIP_DESC SET DATA TYPE VARCHAR(60)
Example 4: Alter the EMPLOYEE table. Add the check constraint named REVENUE defined so that each employee must make a total of salary and commission greater than $30,000.
ALTER TABLE EMPLOYEE ADD CONSTRAINT REVENUE CHECK (SALARY + COMM > 30000)
Example 5: Alter EMPLOYEE table. Drop the constraint REVENUE which was previously defined.
ALTER TABLE EMPLOYEE DROP CONSTRAINT REVENUE
Example 6: Alter the EMPLOYEE table. Alter the column PHONENO to accept up to 20 characters for a phone number.
ALTER TABLE EMPLOYEE ALTER COLUMN PHONENO SET DATA TYPE VARCHAR (20)
Example 7: Alter the base table TRANSCOUNT to a materialized query table. The result of the select-statement must provide a set of columns that match the columns in the existing table (same number of columns and compatible attributes).
ALTER TABLE TRANSCOUNT ADD MATERIALIZED QUERY (SELECT ACCTID, LOCID, YEAR, COUNT(*) AS CNT FROM TRANS GROUP BY ACCTID, LOCID, YEAR ) DATA INITIALLY DEFERRED REFRESH DEFERRED MAINTAINED BY USER
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.