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.
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)
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 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.
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
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')
ALTER TABLE EMPLOYEE DROP PRIMARY KEY (EMPNO)
For more information, see ALTER TABLE in the SQL Reference topic in the
Information Center.