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 cast to a different type, or existing data is evaluated against the new definitions.

The following examples are based on the test table t1.

Columns
The syntax of the ADD COLUMN and ALTER COLUMN phrases are similar to the one shown in the create table page.

Add a Column
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.

Alter the Characteristic of a Column
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 enough 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'.

Drop a Column
Columns can be dropped from existing tables.

Hint: As an extension to the SQL standard, some implementations offer a RENAME or SET INVISIBLE command.

Table Constraints
Table constraints can be added, modified, or dropped. The syntax is similar to that shown on the create table page.

Alter a Table Constraint
Hint: Concerning the change of table constraints, some implementations ignore the syntax of the SQL standard and use other keywords like 'MODIFY'.

Drop a Table Constraint
Hint: As an extension to the SQL standard, some implementations offer an ENABLE / DISABLE command for constraints.

Exercises
Add a column 'col_3' to the table 't1': numeric, not null.

Add a Foreign Key from table 't1' column 'col_3' to table 'person' column 'id'.