<< Click to Display Table of Contents >> ALTER TABLE statement |
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...]
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)