Examples: Alter Table

The following examples add and remove a table-level constraint and a column from the existing base table.

The examples are based on the following table:

create table emp (

name char(10) not null not default,

salary decimal(10,2)

dept char(10),

age integer not null not default);

1. Add a check constraint to ensure that employee ages are correct.

   alter table emp add constraint
   check_age check (age > 0);

2. Drop the age-checking constraint and any dependent constraints.

   alter table emp drop constraint check_age cascade;

3. Add a column to an existing table.

   alter table emp add column location char(10);

4. Drop a column from an existing table.

   alter table emp drop column location restrict;

5. Change the size of a character column.

   alter table emp alter column name char(32);

6. Change the column from a non-Unicode data type to a Unicode data type.

   alter table emp alter column name nchar(32);

7. Change from one character data type to another. For example, from char to varchar.

   alter table emp alter column name varchar(32) not null with default;

8. Change a column from not null to null

   alter table emp alter column name char(32) with null;

9. Change the collation sequence of a column

   alter table emp alter column name nchar(32) not null not default collate unicode_case_insensitive;