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.

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.
•

•
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

"UPDATE Orders " +
•
"SET " +
•
" CustomerID = @NewCustomerID " +
•
"WHERE OrderID = @OldOrderID";