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-Microsoft Jet databases. Use the DAO Create methods instead. Syntax ALTER TABLE table {ADD {COLUMN field type[(size)] [NOT NULL][CONSTRAINT index] | CONSTRAINT multifieldindex} |DROP {COLUMN field I CONSTRAINT indexname} } The ALTER TABLE statement has these partsPart | 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:
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.
If you specify NOT NULL for a field, then new records are required to have valid data in that field.Sub AlterTableX1()
Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")
' Add the Salary field to the Employees table
' and make it a Currency data type.
dbs.Execute "ALTER TABLE Employees " _
& "ADD COLUMN Salary CURRENCY;"
dbs.Close
End Sub
This example removes the Salary field from the Employees table.
Sub AlterTableX2()
Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")
' Delete the Salary field from the Employees table.
dbs.Execute "ALTER TABLE Employees " _
& "DROP COLUMN Salary;"
dbs.Close
End Sub
This example adds a foreign key to the Orders table. The foreign key is based on the EmployeeID field and refers to the EmployeeID field of the Employees table. In this example, you don't have to list the EmployeeID field after the Employees table in the REFERENCES clause because EmployeeID is the primary key of the Employees table.
Sub AlterTableX3()
Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")
' Add a foreign key to the Orders table.
dbs.Execute "ALTER TABLE Orders " _
& "ADD CONSTRAINT OrdersRelationship " _
& "FOREIGN KEY (EmployeeID) " _
& "REFERENCES Employees (EmployeeID);"
dbs.Close
End Sub
This example removes the foreign key from the Orders table.
Sub AlterTableX4()
Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")
' Remove the OrdersRelationship foreign key from
' the Orders table.
dbs.Execute "ALTER TABLE Orders " _
& "DROP CONSTRAINT OrdersRelationship;"
dbs.Close
End Sub
Example (Microsoft Access)
To try the following examples in Microsoft Access, first create a new query in the Northwind sample database. Close the Show Table dialog box without specifying a table or query. Switch to SQL view, paste an individual example into the SQL window, and run the query.
ALTER TABLE Employees ADD COLUMN Salary CURRENCY;
The next example removes the Salary field from the Employees table:
ALTER TABLE Employees DROP COLUMN Salary;
Once you've run the next two queries, click Relationships on the Tools menu to view the changed relationships.
The following example removes the existing foreign key from the Orders table:
ALTER TABLE Orders DROP CONSTRAINT EmployeesOrders;
The next example adds the foreign key back to the Orders table:
ALTER TABLE Orders ADD CONSTRAINT EmployeesOrders
FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID);