DDL - ALTER TABLE
Used to add
or remove columns or constraints.
Exercise:
Add a column to CategoriesNoPix for a short description and then remove
it.
1. Type the following SQL statement in the SQL
design window:
ALTER TABLE CategoriesNoPix
ADD COLUMN ShortDesc Text(25);
2. Execute the statement.. Open the table in Datasheet or Design view to
check the results; close the table.
4. Save as "AddColCategoriesNoPix".
4. Without closing the SQL design window, change
the statement to read
ALTER TABLE CategoriesNoPix
DROP COLUMN ShortDesc;
5. Execute the statement. Open the table to check the result.
6. Save as "DropColCategoriesNoPix".
Exercise: Drop the referential
integrity constraint on the Products table that does not allow a product to be
added if it is not in an existing category; then add the constraint back again.
1. Open the Relationships window and note the
relationship between Categories and Products.
Close the Relationships window.
2. Type the following statement in the SQL
window:
ALTER TABLE Products
DROP CONSTRAINT CategoriesProducts;
3. Execute the statement. Open the Relationships window and check the
result.
4. Save the statement as
“DropProductConstraint”.
5. Modify the statement to add the constraint
back again:
ALTER TABLE Products
ADD CONSTRAINT CategoriesProducts
FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID);
6. Execute the statement. Open the Relationships window and check the
result.
7. Save as “AddProductConstraint”.
Syntax:
ALTER TABLE tablename
ADD COLUMN colname datatype(size) [NOT NULL][CONSTRAINT indexname]
or
ADD CONSTRAINT multicolumnindex
or
DROP COLUMN colname
or
DROP CONSTRAINT indexname