Changing a table definition

 

You change the definition of a table by adding a column, changing an existing column definition, such as its length or default value, dropping an existing column, adding a constraint, or removing a constraint.

To change a table definition, use the SQL ALTER TABLE statement.

You can add, change, or drop columns and add or remove constraints all with one ALTER TABLE statement. However, a single column can be referenced only once in the ADD COLUMN, ALTER COLUMN, and DROP COLUMN clauses. That is, you cannot add a column and then alter that column in the same ALTER TABLE statement.

• Adding a column
  When you add a new column to a table, the column is initialized with its default value for all existing rows. If NOT NULL is specified, a default value must also be specified.

• Changing a column
  You can change a column definition in a table using the ALTER COLUMN clause of the ALTER TABLE statement.

• Allowable conversions of data types
  When you change the data type of an existing column, the old and new attributes must be compatible.

• Deleting a column
  You can delete a column using the DROP COLUMN clause of the ALTER TABLE statement.

• Order of operations for the ALTER TABLE statement
  Operations for the ALTER TABLE statement are performed in a defined order.

 

Parent topic:

Data definition language

 

Related reference

ALTER TABLE