Examples

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:

  EQUIP_NO
INT
  EQUIP_DESC
VARCHAR(50)
  LOCATION
VARCHAR(50)
  EQUIP_OWNER
CHAR(3)

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