Relationships

A relationship is a way of formally defining how two tables relate to each other. When you define a relationship, you are telling the database engine which two fields in two related tables are joined.

The two fields involved in a relationship are the primary key, introduced earlier in this chapter, and the foreign key. The foreign key is the key in the related table that stores a copy of the primary key of the main table.

For example, suppose that you have tables for departments and employees. There is a one-to-many relationship between a department and a group of employees. Every department has its own ID, as does each employee. In order to denote which department an employee works in, however, you must copy the department's ID into each employee's record. So, to identify each employee as a member of a department, the Employees table must have a field say, DepartmentId to store the ID of the department to which that employee belongs. The DepartmentID field in the Employees table is referred to as the foreign key of the Employees table, because it stores a copy of the primary key of the Departments table.

A relationship, then, tells the database engine which two tables are involved and which foreign key is related to which primary key. The old Access/JET engine doesn't require that you explicitly declare relationships, but it's advantageous for you to do so. The reason is that it simplifies the task of retrieving data based on records joined across two or more tables (discussed in more detail in Chapter 2). This lack of declaration is one of the major weaknesses of the JET technology and by far a good reason to upgrade any legacy applications still using JET to use ADO.NET. In addition to matching related records in separate tables, you also need to define a relationship to take advantage of referential integrity, a database engine property that keeps data in a multitable database consistent. When referential integrity exists in a database, the database engine prevents you from removing a record when other records are related to that record in the database.

After you define a relationship in your database, the definition of the relationship is stored until you remove it. You can define relationships graphically, using a database diagram in VS.NET, SQL Enterprise Manager, or Visio, or programmatically with SQL DDL commands.

Using Referential Integrity to Maintain Consistency

When tables are linked through relationships, the data in each table must remain consistent with that in the linked tables. Referential integrity manages this task by keeping track of the relationships among tables and prohibiting certain types of operations on records.

For example, suppose that you have one table called tblCustomer and another table called tblOrder. The two tables are related through a common field the ID field in tblCustomer relates to the CustomerID field in tblOrder.

The premise here is that you create customers that are stored in tblCustomer and then create orders that are stored in tblOrder. But what happens if you run a process that deletes a customer who has outstanding orders stored in the order table? Or what if you create an order that doesn't have a valid CustomerID attached to it? An order without a CustomerID can't be shipped, because the shipping address is a function of the record in tblCustomer. In such a situation the data is said to be in an inconsistent state.

Because your database must not become inconsistent, many database engines (including SQL Server) provide a way for you to define formal relationships among tables, as discussed earlier in this chapter. When you formally define a relationship between two tables, the database engine can monitor the relationship and prohibit any operation that violates referential integrity.

Referential integrity constraints generate application errors whenever the application attempts to perform an action that would leave data in an inconsistent state. For example, in a database with referential integrity activated, if you attempted to create an order that contains a customer ID for a customer who didn't exist, you'd get an error and the order wouldn't be created.

Testing Referential Integrity Constraints, Using Server Explorer

Now that you have a database with the related tblCustomer and tblOrder tables, you can use Server Explorer to examine and test the relationship between them. To do so, follow these steps.

1. Open the database diagram for the Novelty database you created earlier. The two tables, tblCustomer and tblOrder, should appear in the diagram. Note that, although you should have more than two tables in the database, only the first two you created exist in this diagram. To keep diagrams simple, new tables that you create are not automatically added to diagrams. If you want to, you can easily add tables to this diagram to create a complete road map of the database, but for now we're interested only in tblCustomer and tblOrder.

2. Right-click on the relationship, the line connecting the two tables. From the pop-up menu, choose Property Pages.

3. When the property page for this relationship appears, click on the Relationships tab. The relationship denotes a link between the ID field in tblCustomer and the CustomerID field in tblOrder. Toward the bottom of the property page there should also be settings for constraints and cascades.

   Note

   By default, when you create a relationship, the relationship is enforced (for example, you can't create an order for a nonex is tent customer) but isn't cascaded. We discuss cascading in more detail in the next section.

4. Check the box labeled "Cascade Delete Related Records" and then click on the Close button.

5. To save your changes, select the menu command File, Save Relationships.

To test the constraint imposed by the relationship, do the following.

1. In Server Explorer, right-click on tblOrder. From the pop-up menu, select Retrieve Data from Table.

2. Enter an order for a customer with an ID of 9999. Presumably, unless you've been doing an incredible amount of data entry on this table for no reason, there's no customer with an ID of 9999 in the database.

3. Move off the row you're entering to attempt to save it. You should not be successful you should get an error message saying, "INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_tblOrder_tblCustomer'. The conflict occurred in database 'Novelty', table 'tblCustomer', column 'ID'."

4. Cancel the error message and hit the Esc key to abort the record insertion.

There's no need actually to enter the data (the error message is what we were looking for). However, if you needed to create an order for some reason, you'd first need to create a customer, get the ID for that customer, and use it in the CustomerID field when creating an order.

In a real application, this problem would be handled gracefully and automatically you'd typically design a user interface to avoid the problem in the first place. We discuss a variety of strategies to deal with manipulating related records consistently throughout this book.

Cascading Updates and Cascading Deletes

Cascading updates and cascading deletes are useful features of the SQL Server database engine. They cause the following things to happen in your database.

• With cascading updates, when you change a value in a table's primary key, the data in the foreign keys related to that table changes to reflect the change in the primary key. For example, if you had a customer named Halle's and their ID was 72, and you change the ID of Halle's Hockey Mart in the tblCustomer table from 48 to 72, the CustomerID field of all the orders generated by Halle's Hockey Mart in the tblOrder table also changes automatically from 48 to 72. You shouldn't need to change the key of a record (one of the central concepts of keys is that they're unique and immutable), but if you ever do, it's nice to know that cascading updates can do the trick.

• With cascading deletes, when you delete a record in a table, all the records related to that record in other tables also are automatically deleted. Therefore, if you delete the record for Halle's Hockey Mart in the tblCustomer table, all the orders in the tblOrder table for Halle's Hockey Mart are automatically deleted. As you might expect, this use of cascading in a relational database is fairly common.

Note

Be cautious when setting up relationships that perform cascading updates and cascading deletes in your data designs. If you don't plan carefully, you could wind up deleting (or updating) more data than you intended. Some database developers avoid the use of cascades altogether, preferring explicitly to maintain referential integrity across related tables. That's fine, but once you get the hang of how they work, you'll probably find cascades easy to program.

Cascading updates and cascading deletes work only if you've established a relationship between two tables. If you always create tables with AutoNumber or, in SQL Server terms, AutoIncrement primary keys you'll probably find that cascading deletes is more useful than cascading updates. The reason is that you can't change the value of an AutoNumber or AutoIncrement field (so there's no "update" to "cascade").

You can examine how cascading deletes work by using the tools provided by Server Explorer, as follows.

1. Previously you designated the relationship between tblCustomer and tblOrder to support cascading deletes. (If you want to confirm this relationship, use the Database Diagram that you created previously.)

2. Create a customer by right-clicking on tblCustomer in the Tables folder and then selecting Retrieve Data from Table from the pop-up menu. Note the ID that the database engine assigns to the newly created customer; you'll need it shortly when you create orders for this customer. Leave this table open because you'll be returning to it in a moment.

3. Open the tblOrder table and create two or three order records for the customer you just created. To relate each order to the customer, enter the customer's ID in the CustomerID field. Leave this table open as well.

4. Go back to the tblCustomer data-entry grid and delete the customer record by right-clicking on the gray row selector on the far left side of the row and then choosing Delete from the pop-up menu.

5. Visual Studio.NET displays a warning message asking you if you really want to delete the data. Answer Yes.

6. Go back to the tblOrder window. Whoops, you probably expected that the orders you entered for this customer would have been deleted. But they're still there what happened? Actually, they were deleted; you're just looking at an outdated view of the data. To refresh the data, select the menu command Query, Run. The data-entry grid refreshes itself by refetching the data from the database, revealing that the order records for the customer you deleted were automatically deleted thanks to the magic of cascading.