Issues Involved When Updating the Primary Key of a Parent Row

Chia sẻ: Tuan Nghia | Ngày: | Loại File: PDF | Số trang:6

Thêm vào BST

Báo xấu

100
lượt xem 9
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Issues Involved When Updating the Primary Key of a Parent Row In this section, you'll learn about the issues involved when attempting to update the primary key in a parent DataTable

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: 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, you'll learn about the issues involved when attempting to update the primary key in a parent DataTable, and then pushing the update to the underlying database table. The issues occur when the child database table already contains rows that use 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 related through the foreign key on the CustomerID column of the Orders table to the CustomerID column of the Customers table. As you'll learn, you're much better off not allowing changes to the primary key column of a table. If you allow changes to the primary key column, then as you'll see shortly, you can run into problems when pushing the change to the database. Instead, you should set the 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 delete and then recreate the rows in the database with the new primary key and foreign key values. You can control how updates and deletes are performed using the properties of the foreign key in the SQL Server database and also the UpdateRule and DeleteRule properties of a ForeignKeyConstraint object. You'll explore both of these items in the following sections. Controlling Updates and Deletes Using SQL Server You can control how updates and deletes are performed using SQL Server by setting the properties of the foreign key. You set these properties using the Relationships tab of a database table's Properties dialog box. You open this dialog box in Enterprise Manager for 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 named FK_Orders_Customers that contains the details of the foreign key between the Orders and Customers tables. As you can see, these two tables are related through a foreign key on the CustomerID column. Figure 12.1: The Relationships tab for FK_Orders_Customers The Cascade Update Related Fields check box indicates whether a change to a value in the primary key column of the primary key table (the parent table) is also made to the foreign 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 of the Customers table from ALFKI to ANATR; this would also cause the CustomerID column to change from ALFKI to ANATR in the rows of the Orders table. Similarly, the Cascade Delete Related Records check box indicates whether deleting a row in the primary key table also deletes any related rows from the foreign key table. For example, assume this box is checked and you deleted the row with the CustomerID of ANTON from the Customers table; this would cause the rows with the CustomerID of ANTON 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 you'll see shortly, you'll run into problems when pushing changes from your DataSet to the database. Controlling Updates and Deletes Using the UpdateRule and DeleteRule Properties of a ForeignKeyConstraint Object
You can also control updates and deletes using the UpdateRule and DeleteRule properties of a ForeignKeyConstraint object. These properties are of the System.Data.Rule enumeration type; members of this type are shown in Table 12.4. Table 12.4: Rule ENUMERATION MEMBERS CONSTANT DESCRIPTION Cascade 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 in the parent DataTable on which the ForeignKeyConstraint was created, then the same change is also made in any corresponding DataRow objects in the child DataTable. You should set UpdateRule to None in your program; otherwise, as you'll learn in the next section, you'll run into problems when pushing changes from your DataSet to the database. By default, DeleteRule is set to Cascade; therefore, when you delete a DataRow in the parent DataTable, any corresponding DataRow objects in the child DataTable are also deleted. This is fine, as long as you remember to push the deletes to the child table before you push the deletes to the parent table. Updating the Primary Key of a Parent Table and Pushing the Change to the Database In this section you'll learn what happens if you attempt to update the primary key in a parent 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 stored in a DataTable named ordersDT. • The customersDT and ordersDT DataTable objects are related to each other using the following DataRelation: • DataRelation customersOrdersDataRel = • new DataRelation( • "CustomersOrders",
• customersDT.Columns["CustomerID"], • ordersDT.Columns["CustomerID"] • ); • myDataSet.Relations.Add( • customersOrdersDataRel • ); Now, the two settings for the Cascade Update Related Fields check box for FK_Orders_Customers are • Unchecked, meaning that changes to the CustomerID primary key value in the Customers table are not cascaded to the Orders table. This is the default. • Checked, meaning that changes to the CustomerID primary key value in the Customers table are cascaded to the Orders table. In addition, the settings of interest for the UpdateRule property of the ForeignKeyConstraint object added when the earlier DataRelation was created are • Cascade, meaning that changes to the CustomerID DataColumn of customersDT are cascaded to ordersDT. This is the default. • None, meaning that changes to the CustomerID DataColumn of customersDT are not cascaded to ordersDT. Let's examine the three most important cases that vary the checking of the Cascade Update Related Fields box and setting of the UpdateRule property to Cascade and then None. Note You can use the ModifyingRelatedData2.cs program as the basis for trying out the three cases described in this section. First Case Assume the following: • Cascade Update Related Fields box is checked. • UpdateRule is set to Cascade. If you change to the CustomerID DataColumn from J6COM to J7COM and push the change to the database, then the change is made successfully in the customersDT and ordersDT DataTable objects and also in the Customers and Orders database tables. This works as long as you use only the OrderID column in the WHERE clause of the Command object in the UpdateCommand property of your DataAdapter. For example:
ordersUpdateCommand.CommandText = "UPDATE Orders " + "SET " + " CustomerID = @NewCustomerID " + "WHERE OrderID = @OldOrderID"; This UPDATE uses "last one wins" concurrency since only the OrderID primary key column is used in the WHERE clause (the old CustomerID column is left out of the WHERE clause). As mentioned in the previous chapter, "last one wins" concurrency is bad because one user might overwrite a change made by another user. If instead you also include the old CustomerID column value in the WHERE clause of the UPDATE, as shown in the following example, ordersUpdateCommand.CommandText = "UPDATE Orders " + "SET " + " CustomerID = @NewCustomerID " + "WHERE OrderID = @OldOrderID " + "AND CustomerID = @OldCustomerID"; then pushing the change to the database would fail because the original row in the orders table wouldn't be found. The original row wouldn't be found since the CustomerID has already been changed from J6COM to J7COM in the Orders table automatically by the database because Cascade Update Related Fields is checked for the foreign key in the Orders table, but in ordersDT the old CustomerID is set to J6COM. Therefore, the addition of OrderID = @OldOrderID in the WHERE clause prevents the row from being found. Instead, the UPDATE causes a DBConcurrencyException to be thrown. Second Case Assume the following: • Cascade Update Related Fields is unchecked. • UpdateRule is set to Cascade. • The CommandText property of the Command object in the UpdateCommand property of the DataAdapter is set as follows: • ordersUpdateCommand.CommandText = • "UPDATE Orders " + • "SET " + • " CustomerID = @NewCustomerID " + • "WHERE OrderID = @OldOrderID";
If you change the CustomerID from J6COM to J7COM in customersDT and push the change to the database, then the UPDATE will throw a SqlException. This is because the child Orders table currently contains a row with the CustomerID of J6COM, and because of the foreign key you can't change the CustomerID in the parent Customers table. Even if you tried to change the CustomerID in ordersDT first and attempted to push the change to the database, you'd run into the same exception. Third Case Assume the following: • Cascade Update Related Fields is unchecked. • UpdateRule is set to None. • The CommandText of the Command object in the UpdateCommand of the DataAdapter is the same as in the second case. The following code sets the UpdateRule of the ChildKeyConstraint to None: myDataSet.Relations["CustomersOrders"].ChildKeyConstraint.UpdateRule = Rule.None; If you try to change the CustomerID from J6COM to J7COM in customersDT, then you'll throw an InvalidConstraintException. This is because the child ordersDT DataTable currently contains a DataRow with the CustomerID of J6COM, and because of the foreign key you can't change the CustomerID in the parent customersDT DataTable. Even if you tried to change the CustomerID in ordersDT first, you'd run into the same exception. Conclusion The enforcement of the constraints in the previous three examples is correct, and they show what a headache changing the primary key column values can be. The first case is the only one that works, and even then you have to resort to using "last one wins" concurrency in the UPDATE statement, which you should typically avoid. What do you do if you want to change the primary key column value and apply the same change to the child rows? The easiest way is to simply delete the rows in the child table first, change the primary key value in the parent table, and recreate the rows in the child table with the new primary key value.