Structured Query Language/Alter Table

The ALTER TABLE command modifies column definitions and table constraints 'on the fly'. This means existing definitions are extended, changed or deleted or existing data is casted to a different type or existing data is evaluated against the new definitions.

Existing tables can be extended by additional columns with the ADD COLUMN phrase. Within this phrase all options of the original Create Table statement are available: data type, default value, NOT NULL, Primary Key, Unique, Foreign Key, Check.

-- add a new column with any characteristicALTERTABLEt1ADDCOLUMNcol_2VARCHAR(100)CHECK(length(col_2)>5);-- Oracle: The key word 'COLUMN' is not allowed.

With the ALTER COLUMN phrase some characteristics of an existing column can be changed

data type

DEFAULT clause

NOT NULL clause.

The new definitions must be compatible with the old existing data. If you change for example the data type from VARCHAR to NUMERIC this action can only be successful if it is possible to cast all existing VARCHAR data to NUMERIC - the casting of 'xyz' will fail. Casting in the direction from NUMERIC to VARCHAR will be successful as long as the width of the VARCHAR is large enought to store the result.

Hint: Concerning the change of the characteristics of columns some implementations ignore the syntax of the SQL standard and use other keywords like 'MODIFY'.