Deleting Data Using Cascading Delete Relationships

You can simplify things considerably by changing the relationships between the Students, TeacherStudent, and StudentClass tables to support cascading deletes. From SQL Server Enterprise Manager, expand the ClassRecords database and right-click the Diagrams item. Select New Database Diagram from the menu. That launches the Create Database Diagram Wizard. Click the Next button to start the wizard. You'll see the dialog in Figure 14.11.

Figure 14.11: SQL Server's Create Database Diagram Wizard opened on the ClassRecords database

Select the check box titled Add Related Tables Automatically, then select the Students table and click the Add button. The wizard adds three tables to the right list: StudentClass, Students, and TeacherStudent. Click the Next button and then the Finish button to complete the wizard and build the diagram. SQL Server analyzes the tables and their relationships and builds a visual picture showing the tables, fields, and relationships (see Figure 14.12).

Figure 14.12: Database diagram for the Students, TeacherStudent, and StudentClass tables

The lines between the tables are the relationships. Right-click the line between the Students and TeacherStudent table and select Properties from the context menu. The Properties dialog shows the details of the relationship between the two tables. I'm not going to explain this dialog in detail, but near the bottom, you'll see two check boxes. Check the one titled Cascade Delete Related Records. While you're in the dialog, you should also check the Check Existing Data On Creation and Cascade Update Related Fields check boxes. After doing that, click the Close button to close the dialog. Repeat the operation for the relationship between the Students and StudentClass tables. Finally, save the diagram.

Warning You must save the diagram for the changes to take effect.

Note You can also create relationships in SQL Server from the Server Explorer by creating a database diagram, adding tables, and then selecting the relationship lines between the tables. The process is almost exactly the same as the one described for Enterprise Manager. Finally, you can create or change relationships programmatically (see the alter table statement documentation for details).

After changing the relationships, you can delete a row in the Students table, and SQL Server will automatically delete the associated rows in the ClassRecords and TeacherStudent tables. That's the reason for the two radio buttons on the chl4-7.aspx Web Form. You can use multiple delete statements, as you've already seen or, by checking the Use Cascading Deletes radio button, you can delete the student and the associated rows in the TeacherStudent and StudentClass tables using a single delete statement.

Note If you restore the database from the CreateClassRecords.sql file, remember to reapply cascading deletes.

Was this article helpful?

0 0

Post a comment