Modifying a Table Using Enterprise Manager

3 4

As mentioned, modifying a table using Enterprise Manager is simpler and gives you more functionality and flexibility than using T-SQL commands. You can make all your modifications either in the Design Table window or using a database diagram. We'll look at the Design Table window method first. To open the Design Table window for our Bicycle_Sales table, follow these steps:

1. Expand the MyDB database folder in the left pane of Enterprise Manager.
2. Click Tables to display a list of all tables in MyDB in the right pane, as shown in Figure 15-1.

   

   Figure 15-1. Enterprise Manager.

3. Right-click the Bicycle_Sales table in the right pane. Choose Design Table from the shortcut menu to display the Design Table window, shown in Figure 15-2. This window shows the original, unmodified Bicycle_Sales table.

   

   Figure 15-2. The Design Table window.

Altering Columns

To alter a column in the Design Table window, simply click in the appropriate cell or check box in the grid and make the desired change. Each row in the grid represents a column in the table. The headings at the top of the grid tell you which property each cell sets.

For some situations in which T-SQL does not allow you to perform certain modifications and returns an error message, Enterprise Manager provides options that guide you through the process of performing the modifications correctly. For example, if you try to change the data length of a column that has a primary key or foreign key constraint using the ALTER TABLE T-SQL command, you will get an error message similar to this:

 Column or parameter #0: Cannot specify a column width on data type int. 

If you use Enterprise Manager, however, you'll see a message box that enables you to change the data length for that column.

For example, to change the data type of the column make_id (which has a foreign key constraint that references make_id from the Bicycle_Inventory table) from tinyint to smallint, simply click tinyint, click the drop-down arrow to display the Data Type drop-down list, and then select smallint, as shown in Figure 15-3.

Figure 15-3. Changing the data type of a column using Enterprise Manager.

Because the make_id column has a foreign key constraint, the Data Type Change Required dialog box appears, as shown in Figure 15-4. Click Yes to automatically convert the make_id column in both tables from tinyint to smallint.

Figure 15-4. The Data Type Change Required dialog box.

As with T-SQL, when you alter a data type using Enterprise Manager, the original data type must be implicitly convertible to the new data type. If you try to perform an illegal conversion, Enterprise Manager will return an error message similar to the one displayed in Figure 15-5, which shows the result of an illegal attempt to change the data type for the sale_date column from datetime to text. Click OK to close the error message, and then change the incorrect data type to one that is implicitly convertible.

Figure 15-5. Error message displayed after an attempt to change a data type to one that is not implicitly convertible.

To save your changes, click the Save Disk button on the toolbar in the Design Table window. The Save dialog box, shown in Figure 15-6, then confirms that the listed tables are to be written to disk. Click Yes to confirm that you want to save your changes.

Figure 15-6. The Save dialog box.

Adding Columns

To add a column, click in the Column Name column of the first blank row in the Design Table window, type the name of the new column, select its data type, and assign it the appropriate attributes (Allow Nulls, Default Value, Identity, and so on). As shown in Figure 15-7, we've added a column named salesperson_id that is of the data type tinyint, allows null values, and has a default value of 0. Click the Save Disk button to save your changes. SQL Server will now add the new column to the table.

Figure 15-7. Adding a new column named salesperson_id.

Dropping Columns

Dropping, or deleting, a column is a simple process with Enterprise Manager. In the Design Table window, simply right-click the column name or any of its attributes (any cell in the same row as the column name), and choose Delete Column from the shortcut menu. The row will be deleted from the table. Remember to click the Save Disk button to save your changes.

Enterprise Manager will warn you when you are attempting to delete a column that is part of a constraint or an index, when the column has a default value, or when it has a rule bound to it. You will see a message box similar to the one shown in Figure 15-8. Clicking Yes will proceed with the column deletion as well as the deletion of all associated relationships.

Figure 15-8. Message box that appears if you attempt to delete a column that has relationships with other columns and tables.

Creating and Using a Database Diagram

You can also modify tables using a database diagram in Enterprise Manager. To create a database diagram for MyDB with the two sample tables Bicycle_Sales and Bicycle_Inventory, follow these steps:

1. Expand MyDB in the left pane of Enterprise Manager, and then right-click Diagrams. Choose New Database Diagram from the shortcut menu to display the Create Database Diagram Wizard welcome screen, shown in Figure 15-9.

   

   Figure 15-9. The Create Database Diagram Wizard welcome screen.

2. Click Next to display the Select Tables To Be Added screen, shown in Figure 15-10. Select the tables you want to include in your diagram from the Available Tables list, and then click Add. In this example, we have added the Bicycle_Inventory and Bicycle_Sales tables.

   

   Figure 15-10. The Select Tables To Be Added screen.

3. Click Next to display the Completing The Create Database Diagram Wizard screen. Click Finish if the tables you selected are correct, or click Back and make the necessary changes.
4. After you click Finish, you will see your database diagram, shown in Figure 15-11.
5. Save your diagram with a descriptive name by clicking the Save Disk button and typing a name when prompted.

The vertical line that ends with a key and connects the two tables in our diagram represents the foreign key constraint relationship between them. To display the relationship label, right-click in the window background and choose Show Relationship Labels from the shortcut menu. The name of the foreign key constraint appears, as shown in Figure 15-12.

Figure 15-11. Sample database diagram.

Figure 15-12. Viewing table relationship labels.

To select a table, click it; to select more than one table, hold down the Ctrl key and click each table. If you right-click one of the tables and then choose an option from the shortcut menu, that action will be performed on all selected tables. For example, if we select both tables in our database diagram, right-click one of the tables, and then choose Table View and Standard from the shortcut menu, both tables will be modified to display all column properties, as shown in Figure 15-13.

Figure 15-13. Viewing column properties from the database diagram.

The column properties display for each table is similar to the Design Table window, and each table can be modified using the same methods as those used with the Design Table window. Simply add or change the column data in the desired cells and save your changes by clicking the Save Disk button. You can also move and resize the tables in the database diagram, display different views of the tables, and more. Experiment with the various shortcut-menu commands. Notice that as soon as you make a change to a table, an asterisk will appear next to that table name to indicate that a change was made.