ASP.NET 2.0 Visual Web Developer 2005 Express Edition Starter Kit (2006)

Chapter 3

Figure 3-14: Default Value or Binding property

5.The next step is to specify the primary key for the new table. You will recall from the earlier discussion of the database design that this table requires the first three columns to produce a unique value suitable for the primary key. Click on the square gray row selector button at the extreme left of the first column, (fkOrderID) and hold the Shift key down while clicking on the row selector for the third column (ItemSize) so that the top three columns in the list are selected. Then click the Set Primary Key button on the toolbar (as shown in Figure 3-15), or right-click and select Set Primary Key from the context menu. Alternatively, you can select the Set Primary Key option from the main Table Designer menu. Whichever method you choose, a key symbol appears on the rows to indicate that, together, they form the primary key for this table.

84

Building the PPQ Database

Figure 3-15: Selecting the Set Primary Key button

6.The primary key will automatically ensure that rows will appear in the order of the values in the three columns that form the primary key, unless you specify otherwise in a query. However, sometimes it is useful to create indexes on other columns to speed up processing of queries. For example, if you intend to execute many queries against this table that use the value in the ItemName column, you might want to create an index on it. To do this, right-click anywhere inside the Table Designer window and select Indexes/Keys from the context menu (as shown in Figure 3-16), or select Indexes/Keys from the main Table Designer menu.

Figure 3-16: Selecting Indexes/Keys from the context menu

85

Chapter 3

7.This opens the Indexes/Keys dialog, which shows the index named PK_Table1 that was created automatically when the primary key was set on the table. Click the Add button at the bottom of the Indexes/Keys dialog to create a new index named IX_Table1. Make sure that this new index is selected and go to the Columns property in the right-hand window. The first column in the table is selected by default, so click the “three dots” (...) button to open the Index Columns dialog and select the ItemName column. The default sort order is Ascending, as shown in Figure 3-17, which is what you want. You can change this if you want items indexed in reverse order (for example, with a column containing dates or monetary values).

Figure 3-17: Ascending sort order

8.Click OK to close the Index Columns dialog and go back to the Indexes/Keys dialog (see Figure 3-18). Notice the Is Unique property of the new index. Because multiple rows in the table can contain the same value for ItemName (the name of the item ordered), and can also contain NULL values (because you did not turn this off in the column designer window), the values will not be unique, and so the index cannot be unique either. The Is Unique property must be No. For a primary key column, however, or any other column where you specify only unique values can exist, you could create a unique index.

86

Building the PPQ Database

Figure 3-18: Indexes/Keys dialog

An index can be created over multiple columns if required. In the Index Columns dialog, you just add more columns to the grid in the same way as you add columns to the table in the Table Designer window. Click in the empty row below the last Column Name and another drop-down list appears allowing you to select the other columns that will be part of the index. If you go to the Indexes/Keys dialog and select the PK_OrderItems index, you will see the three columns that form this index in the Columns property of the index. You can create as many indexes as you like on a table, but each one has an effect on performance when you add or remove rows in the table. Therefore, you should only create indexes that you will use regularly in queries or when extracting data.

9.Now you can save the new table definition by clicking the Save button on the main toolbar, or by selecting Save Table1 or Save All from the File menu. A dialog appears asking for the name of the new table. Enter OrderItems and click OK. The new table appears in the Data Explorer window (see Figure 3-19).

Figure 3-19: New table in Data Explorer window

87

Chapter 3

Defining Relationships between Tables

You have now completed the tables for the PPQ database, but there is still the task of defining the constraints that implement the relationships between these tables. Just because you created a primary key and a foreign key column in your tables does not mean that SQL Server will respect this. It will freely allow you to add rows to the child table where the foreign key value does not match any parent row primary key, and remove parent rows where there are existing matching child rows.

Both of these situations will cause the data to be inconsistent, because orphan rows (that is, child rows with no matching parent row) will exist. Constraints prevent this happening by checking for the presence of matching primary key and foreign key values in the rows before allowing new child rows to be added or parent rows to be deleted. In doing so, they help to maintain referential integrity of the data. In VWD, you create constraints by specifying the relationships between tables — using either the Table Designer or a database diagram. The easier way is to use a database diagram, but before exploring this in more detail, the following section shows briefly how it is done in the Table Designer.

Defining Relationships in the Table Designer

With a table open in the Table Designer, you can right-click on the Designer window and select Relationships to open the Foreign Key Relationships dialog. This shows any constraints and relationships defined for the selected table. Click Add to a new relationship then select the Tables and Columns Specification property in the right-hand window of this dialog and click the three dots (...) button to open the Tables and Columns dialog (see Figure 3-20).

Figure 3-20: Setting the Tables and Columns Specification for a table relationship

88

Building the PPQ Database

In the Tables and Columns dialog, you specify the primary key table, and select the column in that table that contains the primary key for this relationship. Then you select the column in the current table that contains the foreign key. After clicking OK, the Foreign Key Relationships dialog shows the properties of the new relationship. Notice how the Tables and Columns Specification indicates a relationship between the Orders and OrderItems tables (see Figure 3-21), using the OrderID primary key and the fkOrderID foreign key columns.

Figure 3-21: Viewing the Tables and Columns Specification for a table relationship

Relationships also control how the database will react to changes to the rows in the parent table. This includes deletion of existing parent rows, and (although it is not good practice to do so) changes to the primary key values in the parent table rows. The three options for the Delete Rule and Update Rule (see Figure 3-22) are:

	Rule Action	Deleted Parent Row	Updated Primary Key in Parent Row
	Cascade	Any related rows in the	The value in the foreign key column of any
		child table are deleted.	related child rows is updated to the new
			primary key value.
	Set Null	The value in the foreign	The value in the foreign key column of any
		key column of any related	related child rows is deleted so that the
		child rows is deleted so that	column contains NULL.
		the column contains NULL.
	Set Default	The value in the foreign key	The value in the foreign key column of any
		column of any related child	related child rows is replaced by the default
		rows is replaced by the default	value for that column if one is specified, or
		value for that column if one is	NULL otherwise.
		specified, or NULL otherwise.

89

Chapter 3

Figure 3-22: The Delete Rule and Update Rule Actions

In general, you should avoid ever changing the value of a primary key in any existing row, and handle deletes to a parent table in your code so that you correctly manage the deletion of linked child rows. Doing this means that you can leave the Delete Rule and Update Rule set to No Action. However, the other actions can prove useful in maintaining data integrity in cases where you do not directly manage updates and deletes in your code, for example when using a database row editor or client database tool such as Microsoft Access. The rules will allow you to edit and delete rows, while still maintaining database integrity.

Defining Relationships with a Database Diagram

Having seen the principles of relationships between tables in a database, you will now add the two relationships required for the PPQ database. You created a new table named OrderItems earlier in this chapter, and this table must be related to the existing tables to force referential integrity to be maintained when orders are added, updated, or deleted. You will create these relationships using a database diagram, and you will see that this makes the process much easier than using the Table Designer discussed in the previous section.

1.In the Database Explorer window, right-click on the database diagrams entry and select Add New Diagram (see Figure 3-23).

Figure 3-23: Selecting database diagrams in the Database

Explorer

90

Building the PPQ Database

2.The Database Diagram window opens, together with the Add Table dialog. Click on MenuItems, hold down the Shift key, and click on SizeAndPrice to select all four tables. Then click Add (see Figure 3-24).

Figure 3-24: Add Table window

3.You will see the four tables appear in the Database Diagram window. You will probably have to drag them around to get a tidy layout like that shown in Figure 3-25. Notice that there is an existing relationship between the SizeAndPrice table and the MenuItems table. This is already

present in the example database. You can click on the joining line that represents the relationship and drag it around to get a neat display — it is a good idea to position it so that it points to the appropriate columns in the table as in Figure 3-25. The Database Designer does not do this automatically.

Figure 3-25: Joining line

91

Chapter 3

4.To create a relationship between tables, you drag the primary key column from one table onto the foreign key column in the other table. You need to create a relationship between the MenuItems table and the OrderItems table, so click on the gray “row selector” button at the

left-hand end of the MenuItemID column in the MenuItems table, and then drag it onto the row selector in the fkMenuItemID column in the OrderItems table. This opens both the Foreign Key Relationship and the Tables and Columns dialogs (see Figure 3-26).

Figure 3-26: Foreign Key Relationship and Tables and Columns dialogs

5.Notice that VWD automatically selects the correct tables and columns in this dialog (see Figure 3-26) — it does not do this when you open the Foreign Key Relationship dialog from

the Table Designer. All you need do is confirm that it has selected the correct tables and columns in the Tables and Columns dialogs (if you dragged the correct columns in the diagram window, it will have done so), and click OK. Then, in the Foreign Key Relationship dialog, click OK to create the new relationship (see Figure 3-27).

92

Building the PPQ Database

Figure 3-27: Automatically selecting the correct tables and columns

6.Now repeat the process to create a relationship between the Orders and OrderItems tables. Drag the OrderID column in the Orders table onto the fkOrderID column in the OrderItems table, and click OK in the Tables and Columns and Foreign Key Relationship dialogs. The result will be a diagram like that shown in Figure 3-28.

Figure 3-28: Table relationships

93