ALTER TABLE statement

Use the ALTER TABLE statement to change the structure of an existing table in a database file. You can modify only one column in each statement. The formats of the ALTER TABLE statement are:

Copy
ALTER TABLE table_name ADD [COLUMN] column_definition
ALTER TABLE table_name DROP [COLUMN] unqualified_column_name
ALTER TABLE table_name ALTER [COLUMN] column_definition SET DEFAULT expr 
ALTER TABLE table_name ALTER [COLUMN] column_definition DROP DEFAULT

You must know the table’s structure and how you want to modify it before using the ALTER TABLE statement.

Examples

To

Sample SQL

Add columns

Copy
ALTER TABLE Salespeople ADD C1 VARCHAR

Remove columns

Copy
ALTER TABLE Salespeople DROP C1

Set the default value for a column

Copy
ALTER TABLE Salespeople ALTER Company SET DEFAULT 'Claris'

Remove the default value for a column

Copy
ALTER TABLE Salespeople ALTER Company DROP DEFAULT

Note  SET DEFAULT and DROP DEFAULT do not affect existing rows in the table, but change the default value for rows that are subsequently added to the table.