Adding a Table from a Database Diagram

When you first migrate to SQL Server, you might want to use graphical approaches with the programmatic ones to help expedite your solution development. Database diagrams are a graphical development aid that resemble the Relationship window in Access. However, you can have multiple database diagrams for the same database. In addition, database diagrams have a much richer set of data definition capabilities than the Relationship window does. Think of database diagrams as the Relationship window on steroids!

This section introduces these data definition capabilities by building a new table. Database diagrams are graphical, so I'll present the example with step-by-step instructions. The sample table, Orders_linked_to_MyExtensions , will have three columns , OrderID , OrderDate , and EmployeeID . The OrderID column will be the table's primary key, with an IDENTITY property setting of 10 for its seed and 2 for its increment.

To create a new table with a database diagram, you have to open either a new or an existing diagram. To open a new diagram for a new table from the Database window, follow these steps:

1. Open a diagram by selecting Database Diagrams in the Database window and clicking New.

2. Click Close in the Add Table dialog box because you won't need an existing table to create a new one.

3. Right-click anywhere in the empty diagram, and choose New Table from the shortcut menu.

4. Type the name Orders_linked_to_MyExtensions in the Choose Name dialog box, and click OK.

These steps open a blank table grid that you can use to help design your new table. The grid has a title with the name you assigned to the table, but otherwise it's empty. You can add columns to the table with these steps:

1. Type OrderID in the first Column Name row of the grid. Then select an int data type and clear the Allow Nulls check box because this field will serve as the primary key.

2. Type OrderDate in the second Column Name row. Choose a smalldate data type for this column because this application will be outdated well before 2079 (the last represented by the Smalldate data type) and you don't need to track orders that are less than a minute apart (the most precise Smalldate time unit). Leave the Allow Nulls check box selected.

3. Finish entering columns by typing EmployeeID in the third row of the EmployeeID column. Because this column will eventually serve as a foreign key for linking this table to the MyExtensions table, choose int as the data type so that it matches the data type for EmployeeID in the MyExtensions table.

You're almost finished defining your new table. However, you need to make OrderID the primary key. In addition, if you want the column to automatically populate itself with values, you need to give it an IDENTITY setting. After making these refinements, you can add the new table to the database. Follow these steps to finish creating the table:

1. Right-click anywhere on the OrderID row. Choose Primary Key from the shortcut menu.

2. Click the record selector for OrderID . Right-click a second time in the OrderID row, and choose Properties from the shortcut menu.

3. Select the Columns tab for the Properties dialog box, and confirm that the Column Name setting is OrderID . If not, use the drop-down list for the setting to select OrderID .

4. Next , change the Identity setting to Yes. Then assign the Identity Seed a value of 10 and the Identity Increment a value of 2. (See Figure 11-12.) This will cause OrderID to start at 10 and grow by increments of two for each new record. After making these settings, close the Properties dialog box by clicking Close in the top right corner.

   
   Figure 11.12: The Properties dialog box for the OrderID column that shows the setting for the Identity, Identity Seed, and Identity Increment.

5. Then, click Close for the diagram. Click Yes when prompted about saving changes. Then assign a name to the diagram, such as Adding_Orders_linked_to_MyExtensions.

Linking Two Tables in a Database Diagram

Now let's create a new database diagram that links the Orders_linked_to_MyExtensions table to the MyExtensions table. You can use the diagram to set up referential integrity and cascading updates and deletes. In this example, you don't want to throw away orders based on the status of an employee, but you do want to be able to switch accounts from one employee to the next. Therefore, you enable cascading updates but not cascading deletes.

Let's start the process of linking the tables by creating a new database diagram. (We could use the old diagram, but it is more instructive to start from scratch.) Use the steps on the following page.

1. Open a diagram by selecting Database Diagrams in the Database window and clicking New.

2. From the Add Table dialog box, select MyExtensions . Then click Add.

3. Repeat this process for the Orders_linked_to_MyExtensions table.

4. Click the Close button in the Add Table dialog box.

After rearranging the boxes representing the tables so that they're easy to use, you're ready to start linking the two tables. Follow these steps:

1. Click the record selector for EmployeeID in the MyExtensions table.

2. Drag the EmployeeID field from the MyExtensions table to the EmployeeID field in the Orders_linked_to_MyExtensions table. (See Figure 11-13.)

3. In the Create Relationship dialog box that appears, verify that the primary key table is MyExtensions and that its linking field is EmployeeID . In addition, verify that the foreign key table is Orders_linked_to_MyExtensions and that its linking field is also EmployeeID .

4. Verify that the Enforce Relationship For INSERTs And UPDATEs check box is selected.

5. Select the Cascade Update Related Fields check box.

6. Verify that the Cascade Delete Related Records check box is clear.

7. Click OK to assign the settings in the dialog box.

   
   Figure 11.13: This database diagram shows the graphical link between the EmployeeID field from the MyExtensions table and the EmployeeID field in the Orders_linked_to_MyExtensions table after the Create Relationship dialog box opens but before the establishment of a relationship.

In the last sequence, you specified a foreign key for the Orders_linked_to_MyExtensions table that points at the MyExtensions table. However, Access hasn't yet saved the relationship to the database. You can facilitate this by closing the diagram and saving it. Before you close the diagram, Access will ask whether you want to save your changes. Follow these steps to save the relationship:

1. Click the Close button on your database diagram to close it.

2. Click Yes when prompted about saving your changes.

3. Assign the diagram a name, such as Adding_a_Relationship .

4. Access again asks whether you want to save changes. Click Yes. This prompt also allows you to write the changes to a text file.

There's a lot more to database diagrams, but these two examples can acquaint you with some of the basics and allow you to determine whether using database diagrams fits your personal style of developing. Some Access developers love to code, but others prefer graphical design techniques. If you're in the latter camp, database diagrams might be a good topic for you to explore further.

The following code sample resets the database to remove the objects created with the two database diagram examples you've seen. This permits you to review the steps from scratch, without having to work around previously defined objects, relationships, and diagrams. It also gives you some insight into programmatically managing database diagrams.

This sample has two components . First, it conditionally drops the Orders_linked_to_MyExtensions table. Dropping the table also eliminates its relationship to the MyExtensions table. Then the procedure removes all the diagrams in the database. SQL Server stores information describing its database diagram collection members in the dtproperties table. Although SQL Server defines this as a user -defined table, it's typically managed by SQL Server. In addition, SQL Server provides no explicit means for programmatically referencing database diagrams. However, you can delete the rows in the dtproperties table. Doing so removes the diagrams associated with those rows. This procedure removes the diagrams created in this chapter. The procedure refreshes the Database window with the RefreshDatabaseWindow method.

 SubDrop_Orders_linked_table_and_Diagrams()  Dimstr1AsString Dimcnn1AsADODB.Connection DimTableNameAsString     'Pointaconnectionobjectatthecurrentproject Setcnn1=CurrentProject.Connection     'DeletetheOrder_linked_to_MyExtensionstableifitexistsalready TableName= "Orders_linked_to_MyExtensions" Drop_a_tablecnn1,TableName     'Dropalldatabasediagrams str1= "DELETEFROMdtProperties" cnn1.Executestr1     'RefreshDatabasewindowtoshownewtable RefreshDatabaseWindow     EndSub