ADO.NET 3.5 Cookbook, 2nd Edition

7.6. Avoiding Referential Integrity Problems when Updating Data in Related Tables

Problem

You sometimes get referential integrity errors when you update a DataSet that contains related parent, child, and grandchild records back to the underlying data source, but want to perform the update without errors.

Solution

Use one DataAdapter for each DataTable to update the deleted, updated, and inserted rows as shown in the following example.

The solution uses three tables named Parent, Child, and Grandchild. Execute the following T-SQL batch to create the tables:

	USE AdoDotNet35Cookbook
	GO
	CREATE TABLE Parent (
	    ParentId int NOT NULL IDENTITY (1, 1) PRIMARY KEY,
	    Field1 nvarchar(50) NOT NULL,
	    Field2 nvarchar(50) NOT NULL
	);

	CREATE TABLE Child (
	    ChildId int NOT NULL IDENTITY (1, 1) PRIMARY KEY,
	    ParentId int NOT NULL,
	    Field3 nvarchar(50) NOT NULL,
	    Field4 nvarchar(50) NOT NULL
	);
	CREATE TABLE Grandchild (
	    GrandchildId int NOT NULL IDENTITY (1, 1) PRIMARY KEY,
	    ChildId int NOT NULL,
	    Field5 nvarchar(50) NOT NULL,
	    Field6 nvarchar(50) NOT NULL
	);

	ALTER TABLE Child WITH CHECK
	ADD CONSTRAINT FK_Child_Parent FOREIGN KEY(ParentId)
	REFERENCES Parent (ParentId)
	GO

	ALTER TABLE Grandchild WITH CHECK
	ADD CONSTRAINT FK_Grandchild_Child FOREIGN KEY(ChildId)
	REFERENCES Child (ChildId)
	GO

The schema of table Parent used in this solution is shown in Table 7-1.

Table 7-1. Parent table schema

Column name	Data type	Length	Allow nulls?
ParentId	int	4	No
Field1	nvarchar	50	Yes
Field2	nvarchar	50	Yes

The schema of table Child used in this ...