ALTER TABLE Statement

Description

Modifies the design of a table after it has been created with the CREATE TABLE statement.

Note

The Microsoft Jet database engine doesn't support the use of ALTER TABLE, or any of the data definition language (DDL) statements, with non-Jet databases. Use the data access object Create methods instead.

Syntax

ALTER TABLE table {ADD {COLUMN field type[(size)] [CONSTRAINT index] |
CONSTRAINT multifieldindex} |
DROP {COLUMN field | CONSTRAINT indexname} }

The ALTER TABLE statement has these parts.

Part	Description

table	The name of the table to be altered.
field	The name of the field to be added to or deleted from table.
type	The data type of field.
size	The field size in characters (Text and Binary fields only).
index	The index for field. See the CONSTRAINT clause topic for more information on how to construct this index.
multifieldindex	The definition of a multiple-field index to be added to table. See the CONSTRAINT clause topic for more information on how to construct this clause.
indexname	The name of the multiple-field index to be removed.

Remarks

Using the ALTER TABLE statement, you can alter an existing table in several ways. You can:

Use ADD COLUMN to add a new field to the table. You specify the field name, data type, and (for Text and Binary fields) an optional size. For example, the following statement adds a 25-character Text field called Notes to the Employees table:

ALTER TABLE Employees ADD COLUMN Notes TEXT(25)

You can also define an index on that field. For more information on single-field indexes, see the CONSTRAINT Clause Topic.

Use the ADD constraint to add a multiple-field index. For more information on multiple-field indexes, see the CONSTRAINT Clause Topic.

Use DROP COLUMN to delete a field. You specify only the name of the field.
Use the DROP constraint to delete a multiple-field index. You specify only the index name following the CONSTRAINT reserved word.

Notes

You can't add or delete more than one field or index at a time.
You can use the CREATE INDEX statement to add a single- or multiple-field index to a table, and you can use ALTER TABLE or the DROP statement to delete an index created with ALTER TABLE or CREATE INDEX.

See Also

CONSTRAINT Clause, CREATE INDEX Statement, CREATE TABLE Statement, DROP Statement.

Example

The SQL statements shown below can be used on tables created with the CREATE TABLE statement.

This example adds a Salary field with a data type of Currency to the Employees table.

ALTER TABLE Employees ADD COLUMN Salary CURRENCY;

This example removes the Salary field from the Employees table.

ALTER TABLE Employees DROP COLUMN Salary;

This example adds a foreign key to the Orders table. The foreign key is based on the Employee ID field and refers to the Employee ID field of the Employees table. In this example, you don't have to list the Employee ID field after the Employees table in the REFERENCES clause because Employee ID is the primary key of the Employees table.

ALTER TABLE Orders ADD CONSTRAINT OrdersRelationship FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID);

This example removes the foreign key from the Orders table.

ALTER TABLE Orders DROP CONSTRAINT OrdersRelationship;