STATEMENT:  ALTER TABLE
ALTER TABLE table
{ADD {COLUMN field type[(size)] [NOT NULL] [CONSTRAINT index] |
CONSTRAINT multifieldindex} |
DROP {COLUMN field | CONSTRAINT indexname} }
 
The ALTER TABLE statement can be used to alter an existing table by adding or dropping columns and indexes.
 
You can use ADD COLUMN to add a single field to a table by specifying the field name, data type and, optionally, a size for the Text and Binary fields:
 
ALTER TABLE Sales ADD COLUMN UnitPrice CURRENCY;
 
You can add the reserved words NOT NULL to require valid data to be added to that field:
 
ALTER TABLE Sales ADD COLUMN UnitPrice CURRENCY NOT NULL;
 
...and you can use DROP COLUMN to delete a single field :
 
ALTER TABLE Sales DROP COLUMN UnitPrice;
 
You can also define an index for a new field by using the CONSTRAINT clause:
 
ALTER TABLE Sales ADD COLUMN Item TEXT CONSTRAINT UniqueConstraint UNIQUE;
 
You can use ADD CONSTRAINT to add a multi-field index:
 
ALTER TABLE Names ADD CONSTRAINT UniqueValues UNIQUE (FirstName, LastName);
 
...and DROP CONSTRAINT to remove a multi-field index:
 
ALTER TABLE Names DROP CONSTRAINT UniqueValues;
 
Microsoft warns, "The Microsoft Jet database engine doesn't support the use of any DDL statements with databases produced by any other database engine. Use the DAO (Data Access Objects) Create methods instead."

Copyright 1999-2001 by Infinite Software Solutions, Inc. All rights reserved.
Trademark Information