Maintaining Master-Details Integrity

Working with master-details tables raises the issue of how to keep information in sync that is stored in two places. In database terms, keeping related tables synchronized is referred to as maintaining referential integrity between the tables. For example, you don t want to have a record in the Slides table that has no value in the SlideshowName column or that has a value that refers to a non-existent slideshow. Such records would be orphaned they would be useless, and they would be hard to get rid of.

You might have noticed that I didn t give you a way to delete slideshows in the  CreateSlideshow.aspx page. This wasn t an oversight. It would be easy to add a Delete button to the grid in theCreateSlideshow.aspx page, just as you did for the  AddSlides.aspx page. Ideally, however, you shouldn t delete a slideshow record if the Slides table contains any slides that reference the master slideshow record. Because I didn t show you how to guard against the possibility of orphaning child records, I opted not to include a Delete button on the master page.

I did leave open the possibility of creating orphaned slides in the  AddSlides.aspx page, however. The slideshow name is passed from the master page using a query string. As I ve pointed out, however, a query string is easy to spoof. A user can call the  AddSlides.aspx page and pass a query string with a bogus slideshow name in it. The  AddSlides.aspx page would be none the wiser and would happily allow the user to enter slides for the nonexistent slideshow.

MSDE and SQL Server provide database-level facilities to enforce referential integrity and prevent these types of problems. In MSDE, a knowledgeable user can create a constraint, or rule, that raises an error if you try to delete a master record that still contains related detail records. Alternatively, MSDE can perform a cascading delete, in which all detail records are deleted along with the master record. Another type of constraint would prevent you from creating a slide if the corresponding master slideshow record didn t already exist.

Unfortunately, Web Matrix doesn t give you the tools to define constraints or cascading deletes in MSDE. This limitation puts Web Matrix users in a bit of a bind. You have the following options: First, you can ignore the problem on the theory that for small applications, some extraneous orphaned records are not a big deal. Second, you can use tools other than Web Matrix to create constraints and other referential integrity rules in MSDE or SQL Server. MSDE supports a command-line interface, the osql utility, that allows you to execute Transact-SQL commands to perform database tasks such as adding constraints. SQL Server has a much more sophisticated tool, Enterprise Manager, that provides extensive management capabilities for SQL Server. If you intend to develop database applications more sophisticated than the simple applications we create in this book, you ll want to investigate the possibility of using these tools for referential integrity.

A third option is to build referential integrity into your pages. For example, it s possible to add a Delete button to the  CreateSlideshow.aspx page and then add logic that checks for detail records before allowing the delete operation to complete. Database experts don t recommend putting referential integrity checks into an application; the experts prefer to keep the checks in the database where they belong, and I agree. However, Web Matrix makes that option somewhat difficult, as I ve explained.

Because I promised that this book would not delve too deeply into database issues, I won t show you here how to expand our sample pages to include referential integrity checks. However, I have created versions of the  CreateSlideshow.aspx and  AddSlides.aspx pages that include referential integrity checks. The RI versions of these pages are included on the companion CD for those who want to explore this area further.