Dropping a Table

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.

Using T-SQL to Drop a Table

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.

Using Enterprise Manager to Drop a Table

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:

1. In the left pane of Enterprise Manager, expand the database that contains the table you want to drop and click Tables, and then, in the right pane, right-click the name of the table you want to drop.
2. Choose Delete from the shortcut menu to display the Drop Objects dialog box, shown in Figure 15-14.

   

   Figure 15-14. The Drop Objects dialog box.

3. If the table has any dependent tables, click Show Dependencies to show the Dependencies dialog box (Figure 15-15). Any tables that are dependent on this table will appear in the left list in this dialog box. If there are any dependent tables, you cannot delete this table until those dependencies have been removed.
4. If no other tables are dependent on the selected table, you can delete the table by clicking Drop All in 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:

1. Open the database diagram in Enterprise Manager by expanding the database you want to use, clicking Diagrams, and then double-clicking the appropriate diagram name in the right pane. Select the name of the table you want to delete—in this case, Bicycle_Inventory.
2. Right-click anywhere in the table and choose Delete Table From Database from the shortcut menu. When you are prompted to verify that you want to delete the table from the database, click Yes. The table and the foreign key constraint are removed from the diagram.
3. If you're certain you want to permanently delete this table, save your changes by clicking the Save Disk button. The table will then be deleted from the database. If you change your mind about deleting the table, simply exit the Edit Diagram window without saving your changes by clicking Close. If you then reopen the database diagram, the original data will still be there. No changes take effect until you save your work.