ALTER TABLE statement

<< Click to Display Table of Contents >>

Navigation:  Local SQL > Data Definition Language >

ALTER TABLE statement

Previous pageReturn to chapter overviewNext page
hmtoggle_plus1See also

Adds or deletes a column from a table.

ALTER TABLE table_reference DROP [COLUMN] column_reference | ADD [COLUMN] column_reference [,reference DROP [COLUMN] column_reference | ADD [COLUMN] column_reference...]

Description

Use the ALTER TABLE statement to add a column to or delete a column from an existing table. It is possible to delete one column and add another in the same ALTER TABLE statement.

The DROP keyword requires only the name of the column to be deleted. The ADD keyword requires the same combination of column name, type, and possibly dimension definition as CREATE TABLE when defining new columns. See the section on defining column types for the specific syntax of all supported column types.

The statement below deletes the column FULLNAME and adds the column LASTNAME.

ALTER TABLE "Names.db"

DROP FullName, ADD LastName CHAR(25)

It is possible to delete and add a column of the same name in the same ALTER TABLE statement, however any data in the column is lost in the process. This allows quick redefinition of columns while still in the database design stages.

ALTER TABLE "Names.db"

DROP LastName, ADD LastName CHAR(30)

If a column to be deleted is part of a primary key, the primary index is deleted. ALTER TABLE fails on an attempt to delete a column that is the target of a foreign key constraint (referential integrity).

To reference columns with non-alphanumeric characters or spaces embedded in the column name, you must enclose the column name in quotation marks and prefix the quoted column name with the table name in quotes.

ALTER TABLE "Customer.db"

ADD "Customer.db"."#ID" CHAR(3)