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