Issues Involved When Updating the Primary Key of a Parent Row

Issues Involved When Updating the Primary Key of a Parent Row In this section, youll learn about the issues involved when attempting to update the primary key in a parent DataTable
Nội dung trích xuất từ tài liệu:
Issues Involved When Updating the Primary Key of a Parent RowIssues Involved When Updating the Primary Key of a Parent RowIn this section, youll learn about the issues involved when attempting to update theprimary key in a parent DataTable, and then pushing the update to the underlyingdatabase table. The issues occur when the child database table already contains rows thatuse the primary key you want to change in the parent table.The examples in this section will use the Customers and Orders table, which are relatedthrough the foreign key on the CustomerID column of the Orders table to the CustomerIDcolumn of the Customers table.As youll learn, youre much better off not allowing changes to the primary key column ofa table. If you allow changes to the primary key column, then as youll see shortly, youcan run into problems when pushing the change to the database. Instead, you should setthe ReadOnly property to true for the primary key DataColumn in your parent DataTable,and also set ReadOnly to true for the foreign key DataColumn in your child DataTable.That prevents changes to the values in these DataColumn objects.If you really need to change the primary key and foreign key values, you should deleteand then recreate the rows in the database with the new primary key and foreign keyvalues.You can control how updates and deletes are performed using the properties of theforeign key in the SQL Server database and also the UpdateRule and DeleteRuleproperties of a ForeignKeyConstraint object. Youll explore both of these items in thefollowing sections.Controlling Updates and Deletes Using SQL ServerYou can control how updates and deletes are performed using SQL Server by setting theproperties of the foreign key. You set these properties using the Relationships tab of adatabase tables Properties dialog box. You open this dialog box in Enterprise Managerfor the Orders table by performing the following steps: 1. Right-click the Orders table in the Tables node of Enterprise Manager. 2. Select Design Table from the pop-up list. 3. Press the Manage Relationships button in the toolbar of the Design Table dialog box. 4. Select the foreign key you want to examine in the Select relationship drop-down list.Figure 12.1 shows the Relationships tab for the foreign key namedFK_Orders_Customers that contains the details of the foreign key between the Ordersand Customers tables. As you can see, these two tables are related through a foreign keyon the CustomerID column.Figure 12.1: The Relationships tab for FK_Orders_CustomersThe Cascade Update Related Fields check box indicates whether a change to a value inthe primary key column of the primary key table (the parent table) is also made to theforeign key column of the corresponding rows of the foreign key table (the child table).For example, assume this box is checked and you changed the CustomerID in the row ofthe Customers table from ALFKI to ANATR; this would also cause the CustomerIDcolumn to change from ALFKI to ANATR in the rows of the Orders table.Similarly, the Cascade Delete Related Records check box indicates whether deleting arow in the primary key table also deletes any related rows from the foreign key table. Forexample, assume this box is checked and you deleted the row with the CustomerID ofANTON from the Customers table; this would cause the rows with the CustomerID ofANTON to also be deleted from the Orders table.Note Typically, you should leave both check boxes in their default unchecked state. If you check them, the database will make changes to the rows in the child table behind the scenes and as youll see shortly, youll run into problems when pushing changes from your DataSet to the database.Controlling Updates and Deletes Using the UpdateRule and DeleteRule Properties ofa ForeignKeyConstraint ObjectYou can also control updates and deletes using the UpdateRule and DeleteRule propertiesof a ForeignKeyConstraint object. These properties are of the System.Data.Ruleenumeration type; members of this type are shown in Table 12.4. Table 12.4: Rule ENUMERATION MEMBERSCONSTANT DESCRIPTIONCascade Indicates that the delete or update to the DataRow objects in the parent DataTable are also made in the child DataTable. This is the default.None Indicates that no action takes place.SetDefault Indicates that the DataColumn values in the child DataTable are to be set to the value in the DefaultValue property of the DataColumn.SetNull Indicates that the DataColumn values in the child DataTable are to be set to DBNull.By default, UpdateRule is set to Cascade; therefore, when you change the DataColumn inthe parent DataTable on which the ForeignKeyConstraint was created, then the samechange is also made in any corresponding DataRow objects in the child DataTable. Youshould set UpdateRule to None in your program; otherwise, as youll learn in the nextsection, youll run into problems when pushing changes from your DataSet to thedatabase.By default, DeleteRule is set to Cascade; therefore, when you delete a DataRow in theparent DataTable, any corresponding DataRow objects in the child DataTable are alsodeleted. This is fine, as long as you remember to push the deletes to the child table beforeyou push the deletes to the parent table.Updating the Primary Key of a Parent Table and Pushing the Change to theDatabaseIn this section youll learn what happens if you attempt to update the primary key in aparent table when there are corresponding rows in the child table. Assume the following: • There is a row in the Customers table with a CustomerID of J6COM. A copy of this row is stored in a DataTable named customersDT. • There is a row in the Orders table that also has a CustomerID of J6COM. A copy of this row is sto ...