3 4
When you drop a table, the table definition and data, indexes, constraints, triggers, and permissions associated with that table are all deleted. Views and stored procedures that reference the dropped table must be dropped explicitly. A table cannot be dropped if it is referenced by a foreign key constraint in another table—in other words, a table cannot be dropped if another table depends on it. Either the constraint or the referenced table must be dropped before the table can be deleted. On the other hand, the table that holds the foreign key constraint can be dropped if no other tables depend on it. In this section, we'll look at how to drop a table using T-SQL and Enterprise Manager.
The T-SQL command used to drop a table has the following syntax:
DROP TABLE <table_name>
Use DROP VIEW or DROP PROCEDURE to drop views or stored procedures that reference the dropped table. You can drop the views and stored procedures before or after dropping the table. Once a table has been dropped, you cannot retrieve it—you must re-create the entire table, data and all.
To drop the Bicycle_Inventory table, which is referenced by a foreign key constraint from the Bicycle_Sales table, we must first drop the foreign key constraint, sales_inventory_fk, and then drop the table, as shown here:
ALTER TABLE Bicycle_Sales DROP CONSTRAINT sales_inventory_fk GO DROP TABLE Bicycle_Inventory GO
If you try to drop the table before removing the foreign key constraint, you will get an error message and the table will not be dropped.
Enterprise Manager provides two methods for dropping a table: using the Drop Objects dialog box or using a database diagram. The Drop Objects dialog box method is best when you are deleting a table that does not have any other tables dependent on it. To use this method, follow these steps:
Figure 15-14. The Drop Objects dialog box.
Figure 15-15. The Dependencies dialog box for the Bicycle_Sales table.
To delete a table that does have dependent tables, we'll use the second method: a database diagram. In this example, we'll remove the Bicycle_Inventory table, which is referenced in a foreign key constraint by the Bicycle_Sales table. This deletion would fail if we used the Drop Objects dialog box method. But if we use a database diagram that shows both tables, we can remove either table and the foreign key constraint will be removed automatically. To delete the Bicycle_Inventory table, follow these steps: