ALTER TABLE

The ALTER TABLE statement adds, drops, or alters a column from an existing table; adds unique, referential, or check constraints; or adds or drops a primary, unique, or foreign key.

Examples:

ADD COLUMN

Adds a column to the table.  You must specify a data type for the column.  For a list of data types, see Data types. You can also add an identity column, if one does not already exist in the table.

To add a column:

ALTER TABLE CORPDATA.ORG
  ADD COLUMN COUNTRY_CODE CHAR (6)

To add an identity column:

ALTER TABLE CORPDATA.ORG
  ADD COLUMN NUMDEPT SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY
  (START WITH 1, INCREMENT BY 1)

ALTER COLUMN

Alters the definition of an existing column.  Only the attributes specified will be altered; others will remain unchanged.  You can specify a default value, set the column to NOT NULL, drop the default value, or drop NOT NULL.  You can also cast the data type of the column to a new data type.  However the new data type must be compatible with the old.  For more information about the compatibility of the data types, see Assignments and comparisons in the SQL Reference topic in the Information CenterLink to Information center.

You can also modify the attributes of an existing identity column or drop the identity attribute from a column.

To alter a column:

ALTER TABLE CORPDATA.ORG          
  ALTER COLUMN COUNTRY_CODE
  SET DEFAULT 'USA123'

To alter an identity column:

ALTER TABLE CORPDATA.ORG         
  ALTER COLUMN NUMDEPT         
  DROP IDENTITY

The column NUMDEPT remains, but the identity attribute is dropped.

DROP COLUMN

Drops the identified column from the table.  When you drop a column, you also drop any views, indexes, triggers, or constraints associated with the column.  You can prevent this effect by using the keyword RESTRICT.

To drop a column:

ALTER TABLE CORPDATA.ORG
  DROP COLUMN COUNTRY_CODE		

ADD unique constraint/referential constraint/check constraint

Specifies a constraint on a column.  You can add a unique or primary key, a referential constraint, or a check constraint.

To add a primary key:

ALTER TABLE CORPDATA.DEPARTMENT         
  ADD PRIMARY KEY (DEPTNO)

To add a referential constraint:

ALTER TABLE CORPDATA.EMPLOYEE
  ADD FOREIGN KEY (WORKDEPT)
  REFERENCES CORPDATA.DEPARTMENT 
  ON DELETE SET NULL

To add a check constraint:

ALTER TABLE CORPDATA.EMPLOYEE
  ADD CONSTRAINT NUMBER 
  CHECK (PHONENO >= '0000' AND  PHONENO < = '9999')

DROP CONSTRAINT

ALTER TABLE EMPLOYEE
  DROP PRIMARY KEY (EMPNO)

For more information, see ALTER TABLE in the SQL Reference topic in the Information CenterLink to Information center.